Delete more than one record - 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 2005 Forums

Transact-SQL (2005)

Delete more than one record

Author

Topic

ronedin
Starting Member

29 Posts

Posted - 2007-10-18 : 03:41:33

Hi,

I am writing a query to find nonmatching entries (empid) in 'both' the tables table1 and table2

SELECT empid FROM
(SELECT empid FROM table1
UNION ALL SELECT empid FROM table2) A GROUP BY empid HAVING COUNT(*) = 1

It gives me the desired results.

Problem : I want to delete the records physically now from the table1 and table2 which were returned by the above query.

Something like :

DELETE FROM table1,table2 where empid = (SELECT empid FROM
(SELECT empid FROM table1
UNION ALL SELECT empid FROM table2) A GROUP BY empid HAVING COUNT(*) = 1)

How to write this query? Please suggest me a query by keeping the inner query the same as it gives me the desired results.

thanks

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 03:54:45

You can only delete from one table at a given time.

DECLARE	@Sample TABLE (dt DATETIME, Price MONEY)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	'16/10/2007', 16.75 UNION ALL
SELECT	'16/10/2007', 16.25 UNION ALL
SELECT	'12/10/2007', 15.7 UNION ALL
SELECT	'12/10/2007', 15.64 UNION ALL
SELECT	'12/10/2007', 15.17 UNION ALL
SELECT	'12/10/2007', 14.9 UNION ALL
SELECT	'12/10/2007', 14.75 UNION ALL
SELECT	'04/10/2007', 14.52 UNION ALL
SELECT	'04/10/2007', 14.84 UNION ALL
SELECT	'05/10/2007', 14.9 UNION ALL
SELECT	'01/10/2007', 14.84 UNION ALL
SELECT	'28/09/2007', 15.3

SELECT * FROM @Sample

DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY dt ORDER BY Price DESC) AS RecID
		FROM	@Sample
	) AS f
WHERE	RecID > 1

SELECT * FROM @Sample

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

ronedin
Starting Member

29 Posts

Posted - 2007-10-18 : 04:46:15

Hi peso,

I wrote this

DECLARE @table1 TABLE (empid int, empname nvarchar(20))
DECLARE @table2 TABLE (empid int, empname nvarchar(20))

INSERT @table1
select 1,'emp1' UNION ALL
select 2,'emp2' UNION ALL
select 3,'emp3' UNION ALL
select 4,'emp4' UNION ALL
select 5,'emp5' UNION ALL
select 6,'emp6' UNION ALL
select 7,'emp7' UNION ALL
select 8,'emp8'

INSERT @table2
select 1,'emp1' UNION ALL
select 2,'emp2' UNION ALL
select 13,'emp3' UNION ALL
select 4,'emp4' UNION ALL
select 5,'emp5' UNION ALL
select 6,'emp6' UNION ALL
select 7,'emp7' UNION ALL
select 8,'emp8'

The 3rd row in table1/table2 is not identical. So i want to delete that physically.

I wrote the query like this but it doesnt work.

Delete B FROM (SELECT empid FROM
(SELECT empid FROM @table1
UNION ALL SELECT empid FROM @table2) A GROUP BY empid HAVING COUNT(*) = 1) as B

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 04:50:28

quote:
You can only delete from one table at a given time.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 04:52:02

[code]DECLARE @table1 TABLE (empid int, empname nvarchar(20))
DECLARE @table2 TABLE (empid int, empname nvarchar(20))

INSERT @table1
select 1,'emp1' UNION ALL
select 2,'emp2' UNION ALL
select 3,'emp3' UNION ALL
select 4,'emp4' UNION ALL
select 5,'emp5' UNION ALL
select 6,'emp6' UNION ALL
select 7,'emp7' UNION ALL
select 8,'emp8'

INSERT @table2
select 1,'emp1' UNION ALL
select 2,'emp2' UNION ALL
select 13,'emp3' UNION ALL
select 4,'emp4' UNION ALL
select 5,'emp5' UNION ALL
select 6,'emp6' UNION ALL
select 7,'emp7' UNION ALL
select 8,'emp8'

DELETE t1
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.empid = t1.empid
where t2.empid is null

DELETE t2
FROM @Table2 AS t2
LEFT JOIN @Table1 AS t1 ON t1.empid = t2.empid
where t1.empid is null

select * from @table1
select * from @table2[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 04:58:03

If you try something like this

DELETE	f
FROM	(
		SELECT		COALESCE(t1.EmpID, t2.EmpID) AS EmpID
		FROM		@Table1 AS t1
		FULL JOIN	@Table2 AS t2 ON t2.EmpID = t1.EmpID
		WHERE		t1.EmpID IS NULL
				OR t2.EmpID IS NULL
	) AS f

you will get following error message

Msg 4405, Level 16, State 1, Line 24
View or function 'f' is not updatable because the modification affects multiple base tables.

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

ronedin
Starting Member

29 Posts

Posted - 2007-10-18 : 05:02:27

thanks peso..what does this message mean?

can u recommend me something to learn t-sql..any book or article..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 05:15:56

It means exactly what is says!

quote:
You can only delete from one table at a given time.

Yes!
When you installed SQL Server, you also installed a help file named BOOKS ONLINE.
Read it. It will become your best friend.

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

ronedin
Starting Member

29 Posts

Posted - 2007-10-18 : 05:38:42

ok..i wished u could give me a book name..as every beginner would agree with me that BOL contains way too much info..anyways thanks for the help.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 05:43:31

SQL Team has a list of recommended books. Click on the link that says ... errmmmm ... "Books" in the menu at the top of every page.

You could use the Search facility too ...

... or the FAQ:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Recommended%20books

Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-18 : 06:38:27

wow!! this is a bookmark item..thanks kristen..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources