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:
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.
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy