Need help with DELETE
-
Hello,
I have a situation where I need to delete records in a table called 'tblMMStudentTestScores' based the record's 'Permnum' and 'TestShortName' if the permnum exists in another table called '#tblMMRecordsToDelete'.
The table I need to delete from is called 'tblMMStudentTestScores'. The 'TestShortName' values I need to qualify in this table are 'HMLM' and 'HMRM'.
The matching records are in a table called '#tblMMRecordsToDelete'. Every 'Permnum' in this table will have a record for each 'HMLM' or 'HMRM' TestShortName, so there are two records for each Permnum. The total number of records in this table is 1882.
Here is a query that I tired to return the records that would qualify for the deletion:
Select TS.Permnum from tblMMStudentTestScores TS
Where Exists
(Select DL.MTPermnum From #MMTestsToDelete DL
Inner Join tblMMStudentTestScores TS On TS.Permnum = DL.MTPermnum
and DL.MTTestShortName=TS.TestShortName)
and
(TS.TestShortName = 'HMRM'
or
TS.TestShortName = 'HMLM')
order by TS.Permnum, TS.TestShortName
The inner query returns the correct number of records, 1882. When combined with the outer query, I get 9924 rows back.
The thing I need to do is to delete only the 1882 records returned by the sub query from tblMMStudentTestScores.
How do I accomplish this?
Please let me know if you need more information.
Thanks for your help!
CSDunn
-
Is this what you are after?
DELETE FROM tblMMStudentTestScores
FROM #MMTestsToDelete DL
Where (tblMMStudentTestScores.TestShortName = 'HMRM' OR
tblMMStudentTestScores.TestShortName = 'HMLM')
AND
(tblMMStudentTestScores.Permnum = DL.MTPermnum AND
tblMMStudentTestScores.TestShortName = DL.MTTestShortName)
* Noel -
Thanks for your help! I had not used Delete in this way before, and so I took a look at BOL after performing the Delete.
CSDunn
-
every time you need to delete a lot of records it is a good thing to make a back up first
that way if you are not sure there is always a rollback 
once you get use to the syntax life is good
* Noel -
Just a minor point ... I've found that when testing a field for a number of values, the IN function is more manageable and easier to read than a series of OR statements.
When using the OR statements you have to deal with the order of operations related to any other OR or AND clauses in your SQL. Using IN guarantees that you won't make a mistake by omitting parentheses around your multiple OR's.
For example, instead of: If TS.Field1 = 'something' OR TS.Field1 = 'something else' OR TS.Field1 = 'a third thing'
You can code: If TS.Field1 IN ('something', 'something else', 'a third thing')
Dana
Connecticut, USA
Dana -
This may help for the future. When doing deletes that involve joins I first create a select query (using the table to delete from as the main table) to check that the correct rows are returned as you did. So for your query I think it would look like this
SELECT TS.Permnum, TS.TestShortName
FROM tblMMStudentTestScores TS
INNER JOIN #MMTestsToDelete DL
ON DL.MTPermnum = TS.Permnum
AND DL.MTTestShortName = TS.TestShortName
WHERE TS.TestShortName IN ('HMRM','HMLM')
If all OK then I replace the first line with a delete statement
DELETE TS
FROM tblMMStudentTestScores TS
INNER JOIN #MMTestsToDelete DL
ON DL.MTPermnum = TS.Permnum
AND DL.MTTestShortName = TS.TestShortName
WHERE TS.TestShortName IN ('HMRM','HMLM')
Far away is close at hand in the images of elsewhere.
Anon. -
I do what david posted above a lot but if this is going to hit production (for me) BACKUP goes first
* Noel -
Exactly. In this case, I took the records I intended to delete, and inserted them into a 'backup' table. Also, I worked through the senario working with the data in temporary tables before I applied the solution to the live data to make sure I got the results I expected.
CSDunn
-
Thanks for this input. I have seen this technique before, but I will document it this time.
CSDunn
-
Good. when you are doing something in production ALWAYS, ALWAYS,ALWAYS ... did I said ALWAYS
make sure you have a rollback
* Noel -
What if I needed to use Wildcards in my OR conditions:
SELECT firstname, lastname from Teacher_data_main
WHERE
(Firstname like 'F%'
or
Firstname like 'B%'
or
Firstname like 'C%')
AND
Schoolnum = 371
Thanks!
CSDunn
-
SELECT firstname, lastname from Teacher_data_main
WHERE
(Firstname like '[FBC]%')
AND
Schoolnum = 371
* Noel -
Thanks again!
CSDunn
-
Do you have any good SQL Code reference books that you would recommend? I find it hard to locate stuff like this in BOL.
Thanks!
CSDunn
-
For syntax BOL is pretty good you just have to read sometimes more that once
what is explained on each statement ex:FROM BOL (Like Statement):
Wildcard character Description Example % Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title. _ (underscore) Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on). [ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.
* Noel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply