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
 Old Forums
 CLOSED - General SQL Server
 delete on basis of join

Author  Topic 

style2joy
Starting Member

8 Posts

Posted - 2006-05-17 : 10:06:09
i want to delete all the rows which can be selected using the following statement. ( pls help me convert this select statement to delete statement )

Rows to be deleted are from _cattn table only.


SELECT
dbo._cattn.*, dbo.Users.adoj, dbo.Users.adol, dbo.Users.aName
FROM dbo._cattn INNER JOIN
dbo.Users ON dbo._cattn._cacode = dbo.Users.aCode
WHERE
( (dbo._cattn._cdate < dbo.Users.adoj) OR
(dbo._cattn._cdate > dbo.Users.adol) )

Kristen
Test

22859 Posts

Posted - 2006-05-17 : 10:14:13
You can only delete from one table or the other, so if you want to delete from both tables that will make it a bit more complicated:

DELETE T1
SELECT
T1.*, T2.adoj, T2.adol, T2.aName

FROM dbo._cattn AS T1
INNER JOIN dbo.Users AS T2
ON T1_cacode = T2.aCode
WHERE
( (T1._cdate < T2.adoj) OR
(T1._cdate > T2.adol) )

Kristen
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 10:14:14
Before running this backup the tables involved into seperate tables for safety


SELECT
dbo._cattn.*, dbo.Users.adoj, dbo.Users.adol, dbo.Users.aName


Delete dbo._cattn FROM dbo._cattn INNER JOIN
dbo.Users ON dbo._cattn._cacode = dbo.Users.aCode
WHERE
( (dbo._cattn._cdate < dbo.Users.adoj) OR
(dbo._cattn._cdate > dbo.Users.adol) )




Srinika
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-17 : 10:14:23
Something like

DELETE FROM dbo._cattn
FROM dbo._cattn
INNER JOIN
dbo.Users ON dbo._cattn._cacode = dbo.Users.aCode
WHERE
( (dbo._cattn._cdate < dbo.Users.adoj) OR
(dbo._cattn._cdate > dbo.Users.adol) )


-------
Moo. :)
Go to Top of Page

style2joy
Starting Member

8 Posts

Posted - 2006-05-17 : 12:07:04
Cheers!

Thanks Kristen,

It worked great!!!

thanks
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-18 : 01:38:30
quote:
Originally posted by Kristen

You can only delete from one table or the other, so if you want to delete from both tables that will make it a bit more complicated



Can we delete records from 2 tables in one single delete Query?

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 02:12:07
"Can we delete records from 2 tables in one single delete Query?"

You could have a cascading delete on an FK, or a trigger, but that would be for all deletes, rather than a one-off "Get these out of my database" !!

For the later I reckon you would need to copy PKs into a temporary table, then do two deletes - one from each table.

Kristen
Go to Top of Page
   

- Advertisement -