February 1, 2012 at 9:53 pm
Hi All,
I have a question around "What is the best practice" for doing a delete statement when joining 2 or more tables?
Example:
- Table1 is an original item table consisting of 1000 items
- Table2 is a table I've created by importing an excel spreadsheet consiting of 500 items.
All 500 items from table2 (excel sheet) need to be deleted from table1 so in theory I thought I'll do a join between the tables and then when there is a match delete it from table1 while table2 will not be affected.
I am able to get the join between tables and achieve the results but i am then stuck on the delete side. I don't know how to use it in this context.
SELECT * FROM [Database1].[dbo].[table1] AS t1
INNER JOIN [Database1].[dbo].[table2] AS t2 ON
t1.item1 = t2.item1
I did the below to achieve the result I wanted and it worked, but i dont think this is the best way & secondly I thought it leaves plenty of roomfor error to accidently delete all items from table one if a mistake was done.
DELETE
FROM [Database1].[dbo].[table1]
WHERE item1 IN
(
SELECT item1
FROM [Database1].[dbo].[table2]
)
Is the above the best way or only way to do it, or can my join be modified to do a delete as well in a safer process.
Thanks
Tava
February 2, 2012 at 1:47 am
personall i prefer a join - like so:
DELETE a
FROMtablea a
INNER JOINtableb b
ONa.column = b.column
WHEREcondition
As far as standards go - i'm not too sure, I suppose it would depend on what your company standards state - my company standards state the above.
February 2, 2012 at 2:22 am
The IN syntax is one of the available 'standard' SQL syntaxes; SQL Server also supports a Transact-SQL extension:
-- Original query
SELECT * FROM [dbo].[table1] AS t1
JOIN [dbo].[table2] AS t2 ON
t2.item1 = t1.item1;
-- Transact-SQL extension syntax
DELETE
FROM t1
FROM [dbo].[table1] AS t1
JOIN [dbo].[table2] AS t2 ON
t2.item1 = t1.item1;
-- 'Standard' SQL
DELETE [dbo].[table1]
WHERE
EXISTS
(
SELECT *
FROM [dbo].[table2] AS t2
WHERE t2.item1 = t1.item1
);
There is a suggestion to deprecate the extension, but quite a bit of opposition to the idea too:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 8:42 pm
Thanks for that, they appear to be alot cleaner in layout than the one i was using.
February 3, 2012 at 2:28 pm
There is also the MERGE statement.
February 3, 2012 at 3:20 pm
Lamprey13 (2/3/2012)
There is also the MERGE statement.
CREATE TABLE t1 (a INT)
INSERT t1 VALUES (1),(2),(3),(4),(5)
CREATE TABLE t2 (a INT)
INSERT t2 VALUES (1),(2)
MERGE t1
USING t2 ON t1.a = t2.a
WHEN MATCHED THEN DELETE;
SELECT *
FROM t1
I hadn't thought of using MERGE when only type of operation could result, but it's valid.
February 4, 2012 at 9:35 pm
Stephanie Giovannini (2/3/2012)
I hadn't thought of using MERGE when only type of operation could result, but it's valid.
Not only valid, it can be extremely useful: MERGE checks for double-row updates/deletes that the non-standard join syntax allows with potentially random results. That said, MERGE is not as optimized for performance, so you're usually better off writing a careful single-operation statement instead of MERGE.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2012 at 11:11 pm
CELKO (2/4/2012)
The best practice is not to write dialect SQL and stick to ANSI/ISO syntax
Heh... you've been saying that for years but I've never seen you post a free link. Do you have one you'd care to share?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply