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.
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.aNameFROM dbo._cattn INNER JOIN dbo.Users ON dbo._cattn._cacode = dbo.Users.aCodeWHERE ( (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 T1SELECT T1.*, T2.adoj, T2.adol, T2.aNameFROM dbo._cattn AS T1 INNER JOIN dbo.Users AS T2 ON T1_cacode = T2.aCodeWHERE ( (T1._cdate < T2.adoj) OR (T1._cdate > T2.adol) ) Kristen |
|
|
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 safetySELECT dbo._cattn.*, dbo.Users.adoj, dbo.Users.adol, dbo.Users.aNameDelete dbo._cattn FROM dbo._cattn INNER JOIN dbo.Users ON dbo._cattn._cacode = dbo.Users.aCodeWHERE ( (dbo._cattn._cdate < dbo.Users.adoj) OR (dbo._cattn._cdate > dbo.Users.adol) ) Srinika |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-17 : 10:14:23
|
Something likeDELETE FROM dbo._cattnFROM dbo._cattnINNER JOIN dbo.Users ON dbo._cattn._cacode = dbo.Users.aCodeWHERE ( (dbo._cattn._cdate < dbo.Users.adoj) OR (dbo._cattn._cdate > dbo.Users.adol) )-------Moo. :) |
|
|
style2joy
Starting Member
8 Posts |
Posted - 2006-05-17 : 12:07:04
|
Cheers!Thanks Kristen,It worked great!!!thanks |
|
|
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. |
|
|
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 |
|
|
|
|
|
|
|