Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeJotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

delete record does not exist in table

I want to delete records in table1 if account number does not exist in table
2.
I have following code:
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
Should the above code work?
Any informaion is great appreciated,Yes, that should work just fine...
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2)
You can also use Not Exists
DELETE TABLE1 T1
WHERE Not Exists
(SELECT * FROM TABLE2
Where ACCOUNT_NUMBER =
T1.ACCOUNT_NUMBER)
"Souris" wrote:

> I want to delete records in table1 if account number does not exist in tab
le2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||To add to CBretana's response, your NOT IN construct will work as expected
unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
when there are one or more NULL values. Personally, I prefer NOT EXISTS
because that produces the behavior desired in most situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>I want to delete records in table1 if account number does not exist in
>table2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||Dan
I agree , but if you change 'a little bit :-)' his query it should work as
well as NOT EXISTS .
People just forget with NOT IN to add WHERE condition with an outer table.
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2 Where TABLE1.ACCOUNT_NUMBER =
TABLE2 .ACCOUNT_NUMBER)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OlRMglaRFHA.252@.TK2MSFTNGP12.phx.gbl...
> To add to CBretana's response, your NOT IN construct will work as expected
> unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
> when there are one or more NULL values. Personally, I prefer NOT EXISTS
> because that produces the behavior desired in most situations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000
Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I th
en
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more tha
n
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John