deleting in tableA the rows belonging to tableB

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

Transact-SQL (2005)

deleting in tableA the rows belonging to tableB

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-09-26 : 16:28:04

Hi there,

I get the tableB by doing a select from TableA (TableB is a subset of TableA)

How do I delete the rows from TableA corresponding to the rows in TableB

I kind of want to delete the subset TableB from teh original TableA

TableA and TableB have of course the same columns (col1, Col2, Col3, Col4, Col5)

Thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 16:29:58

delete a
from tablea as a
inner join tableb as b on b.pkcol = a.pkcol

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 16:33:59

Or why not do the both thing at once?

DECLARE	@TableA TABLE (i INT)

INSERT	@TableA
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3

DECLARE	@TableB TABLE (i INT)

SELECT * FROM @TableA
SELECT * FROM @TableB

DELETE	a
OUTPUT	deleted.i
INTO	@TableB
FROM	@TableA AS a
WHERE	i = 2

SELECT * FROM @TableA
SELECT * FROM @TableB

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 16:37:07

Or insert into two tables with ONE insert statement?

DECLARE	@TableA TABLE (i INT)
DECLARE	@TableB TABLE (i INT)

SELECT * FROM @TableA
SELECT * FROM @TableB

INSERT	@TableA
OUTPUT	inserted.i
INTO	@TableB
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3

SELECT * FROM @TableA
SELECT * FROM @TableB

E 12°55'05.25"
N 56°04'39.16"

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-09-26 : 16:51:34

Thank you I will try it.
My table has more than one column so I guess I replace:
on b.pkcol = a.pkcol
by
on b.pkcol1 = a.pkcol1 and b.pkcol2 = a.pkcol2 and b.pkcol3 = a.pkcol3
Right?

Thank you very much

P.S: i didn t understand the last two suggestions but I will try them.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 07:29:44

pkcol means your Primary Key column.

If your Primary Key is multi column, then yes, you need to join on each one.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources