1 more question - 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 2008 Forums

Transact-SQL (2008)

1 more question

Author

Topic

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 07:39:37

MERGE Table1 AS t1
USING (SELECT Name FROM Table2) AS t2
ON t1.Name != t2.Name
WHEN MATCHED THEN DELETE;

Why this query doesn't work properly?
P.S. I have 5 names in both tables and I need remove all Names from t1 if they exist in t2...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-09 : 07:46:15

Try:

MERGE Table1 AS t1
USING (SELECT Name FROM Table2) AS t2
ON t1.Name = t2.Name
WHEN MATCHED THEN DELETE;

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 07:58:37

quote:
Originally posted by robvolk

Try:

MERGE Table1 AS t1
USING (SELECT Name FROM Table2) AS t2
ON t1.Name = t2.Name
WHEN MATCHED THEN DELETE;

But it will delete names, which exist in both tables.
I need to leave repeating names and remove unique names accordingly to second table

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-09 : 08:04:53

quote:
But it will delete names, which exist in both tables.
I need to leave repeating names and remove unique names accordingly to second table

Well that wasn't at all clear from your post. I suggest you post sample data and expected results so we can troubleshoot.

Do you have to use MERGE? There are other, simpler ways to do what you describe, especially if you only have to DELETE.

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 08:10:08

quote:
Originally posted by robvolk

[quote]
Do you have to use MERGE? There are other, simpler ways to do what you describe, especially if you only have to DELETE.

There are two tables InventoryStudents and CurrentInventoryStudents (you can build and fill in these tables using the attached script). The InventoryStudents table contains the list of students that have studied at the University over the past year with their course specify. The CurrentInventoryStudents table contains the list of students who moved to the next course and new students with new their course specify.
You should write one and only one SQL state which will do the following:
delete from InventoryStudents those students that aren't found in CurrentInventoryStudents

create table InventoryStudents
(ID int identity(1,1) not null primary key,
StudentsName varchar(200) not null,
StudyYear smallint not null);

create table CurrentInventoryStudents
(ID int identity(1,1) not null primary key,
StudentsName varchar(200) not null,
StudyYear smallint not null);

insert into InventoryStudents
(StudentsName,StudyYear)
values
('Ivanov',1),
('Ivanova',5),
('Petrov',2),
('Sidorov',4),
('Gorin',5)

insert into CurrentInventoryStudents
(StudentsName,StudyYear)
values
('Ivanov',2),
('Sidorov',5),
('Semenov',1),
('Levin',5),
('Semonov',2)

This is my initial task

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-09 : 08:52:45

Duplicate thread - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160394

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources