Delete records from 2 tables using join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Delete records from 2 tables using join

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-06 : 14:34:28

Good afternoon, I am trying to delete rows from 2 tables at the same time but my query keeps giving me 'incorrect syntax'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Here is the query sample:

DELETE FROM Base, BaseExt
FROM [dbo].[matterBase] Base
inner join [dbo].[matterExtensionBase] BaseExt
ON Base.matterId=BaseExt.matterId
WHERE BaseExt.ClientName is null and BaseExt.MatterName is null
AND BaseExt.matterId = '98010B15-21BD-DF11-ACF4-005056BE3808'
-- For Testing

Thanks in advance for the help

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 14:39:17

quote:
Originally posted by bholmstrom

Good afternoon, I am trying to delete rows from 2 tables at the same time but my query keeps giving me 'incorrect syntax'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Here is the query sample:

DELETE FROM Base, BaseExt
FROM [dbo].[matterBase] Base
inner join [dbo].[matterExtensionBase] BaseExt
ON Base.matterId=BaseExt.matterId
WHERE BaseExt.ClientName is null and BaseExt.MatterName is null
AND BaseExt.matterId = '98010B15-21BD-DF11-ACF4-005056BE3808'
-- For Testing

Thanks in advance for the help

Bryan Holmstrom

T-SQL allows you to delete data from only one table in one statement. So do it as two deletes - in your example, you would delete the Base table first and then the BaseExt table. If you need the operation to be atomic, wrap the queries in a transaction (and try/catch block and take update locks on the BaseExt table).

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-06 : 14:55:25

The only problem with that is the Base Table has a PK Key to BaseExt. If I delete BaseExt first then I have no way of going back to the Base table and deleting the rows since I wont have the matterid anymore. Any ideas?

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 15:29:17

SQL supports cascade deletes which might be an option for you. While it works well and many people use it, I am not a fan of it because it doesn't seem transparent enough to me. If you are interested in that option: http://msdn.microsoft.com/en-us/library/ms186973(v=sql.105).aspx

I didn't fully understand your description, but if you are not able to delete Base table first, that sounds like there is some kind of circular dependency, which should not happen.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-06 : 21:42:35

quote:
Originally posted by bholmstrom

The only problem with that is the Base Table has a PK Key to BaseExt. If I delete BaseExt first then I have no way of going back to the Base table and deleting the rows since I wont have the matterid anymore. Any ideas?

Bryan Holmstrom

Why not store that matterid in a variable ?

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-07 : 06:00:09

First note (primary) keys of both tables based on the where condition.. then delete it from one table and after that delete it from other tables.

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 06:16:47

or capture the pks using OUTPUT clause to table variable in first delete
Then make use of that table for deleting from second

something like


DECLARE @DELETED_ITEMS table
(
matterid int
)

DELETE BaseExt
OUTPUT DELETED.matterid INTO @DELETED_ITEMS 
FROM [dbo].[matterExtensionBase] BaseExt
WHERE BaseExt.ClientName is null and BaseExt.MatterName is null
AND BaseExt.matterId = '98010B15-21BD-DF11-ACF4-005056BE3808' 

DELETE b
FROM Base b
INNER JOIN @DELETED_ITEMS i
ON i.matterid = b.matterid

if you're doing this just for single matterid you can even manually note down value and use it for second delete

the above comes handy for multi records deleted having a variety of matterid values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-07 : 09:07:11

Thanks guys this all helped

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 10:38:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources