Need help in 1 stupid task( - 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)

Need help in 1 stupid task(

Author

Topic

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 06:25:22

Stupid question:
SELECT T1.Name
FROM T1 INNER JOIN T2
WHERE T1.Name = T2.Name;
It'll show fields with same Name from both tables.
I need SELECT Names from T1, that not found in T2
Later I'll put it inside delete from T1 where exists(...);
What is wrong of using != instead of = in WHERE Clause and how should correct query looks?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:33:40

use newly introduced MERGE statement
http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

--------------------------
http://connectsql.blogspot.com/

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 06:47:11

For variant a I wrote this merge:

MERGE CurrentInventoryStudents AS cur
USING (SELECT StudentsName,StudyYear FROM InventoryStudents) AS inv
ON cur.StudentsName = inv.StudentsName
WHEN NOT MATCHED THEN
INSERT(StudentsName,StudyYear)
VALUES();

What should I write in VALUES or instead of it to add all fields from column StudentsName from table CurrentInventoryStudents, that they will be true for all written conditions? And Should I write anything or simply remove VALUES() ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:53:29

use values when you need insert already known values
INSERT(StudentsName,StudyYear)
VALUES('John',2011);

But most of the time we use SELECT to get records from other table/tables
INSERT(StudentsName,StudyYear)
SELECT temp_StudentName,temp_StudentYear
FROM temp

--------------------------
http://connectsql.blogspot.com/

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 06:54:20

MERGE InventoryStudents AS inv
USING (SELECT StudentsName,StudyYear FROM CurrentInventoryStudents) AS cur
ON cur.StudentsName = inv.StudentsName
WHEN NOT MATCHED THEN
INSERT(StudentsName, StudyYear)
VALUES(cur.StudentsName, cur.StudyYear);

I did it! It works!))
lionofdezert thank you!!!

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 07:13:40

MERGE InventoryStudents AS inv
USING (SELECT StudentsName FROM CurrentInventoryStudents) AS cur
ON inv.StudentsName != cur.StudentsName
WHEN MATCHED THEN DELETE;

This statement removes all rows instead of 3, that exist in inventory table and doesn't exist in currentinventory table. Why?
What's wrong?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

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

You're joining with an inequality. Basically you're saying, if a row is in InventoryStudents and does not equal every single row in currentinventory, delete it.

Doubt that's what you want.

The join should almost always be an =, and then you want a WHEN NOT MATCHED BY SOURCE (I think) DELETE.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-09 : 08:54:12

p.s. If all you need to do is delete, merge is completely the wrong tool. You just need a simple, straightforward delete, probably with a NOT EXISTS check.

--
Gail Shaw
SQL Server MVP

PRANCSTER
Starting Member

10 Posts

Posted - 2011-05-09 : 09:21:06

quote:
Originally posted by GilaMonster

p.s. If all you need to do is delete, merge is completely the wrong tool. You just need a simple, straightforward delete, probably with a NOT EXISTS check.

I never met NOT EXISTS check, can you provide me an example?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-09 : 09:26:51

Have you looked in books online (the SQL help file)? I know there are examples in there (look under EXISTS)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources