Simple Delete (hopefuly..) - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Simple Delete (hopefuly..)

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-09-05 : 06:38:54

Hi all,

I have two tables, the second of which reference the first via an ID code. I'd like to setup a trigger on table1 that, when a delete is performed on table1, the trigger performs a delete on table2' record with the same ID code. For example..

Table1
IDCode
123
361
367

Table2
IDCode
123
361
367

So when record 123 is deleted out of table1, the same record in table2 is deleted. Would it be along the lines of..

Delete from table2
inner join table1
on table1.idcode = table2.idcode

Thanks

Blatch.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:08:01

The code above would delete all records that DOES exist.

DELETE     t2
FROM       Table2 t2
WHERE      t2.IdCode NOT IN (SELECT t1.IdCode FROM Table1 t1)

DELETE     t2
FROM       Table2 t2
LEFT JOIN  Table1 t1 ON t1.IdCode = t2.IdCode
WHERE      t1.IdCode IS NULL

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 07:09:14

It will be like...

Delete Table2
from table2 inner join table1
on table1.idcode = table2.idcode

because SQL server needs to understand the table from which the data to be deleted. Why don't you go for CASCADE DELETE option, provided there is foreign key relationship between these two tables?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:13:12

That would also delete all existing rows that have match.

With a trigger, use

DELETE t2
FROM   Table2 t2
WHERE  t2.IdCode IN (SELECT d.IdCode FROM deleted d)

Peter Larsson
Helsingborg, Sweden

cblatchford
Starting Member

40 Posts

Posted - 2006-09-05 : 07:19:07

Thanks both; there is a foreign key constraint but I've gone with Peso first option..

ta

blatch

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:56:43

If you really consider a trigger,

DELETE t2
FROM   Table2 t2
WHERE  t2.IdCode IN (SELECT d.IdCode FROM deleted d)

is the best option.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-05 : 08:22:26

How about using Cascade delete ???

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 08:32:07

quote:
Originally posted by chiragkhabaria

How about using Cascade delete ???

Chirag

It's amazing to find two professionals thinking along the same way !

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources