delete rows from one table that exist in another

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

General SQL Server Forums

New to SQL Server Programming

delete rows from one table that exist in another

Author

Topic

gogetsome
Starting Member

10 Posts

Posted - 2005-08-26 : 14:26:09

Hello,

I need to delete rows from one table that appear in another table.
For example:
newnames has fname, lname, age
oldnames has fname, lname, age

So, I want to be able to remove the rows from newname where there are rows in oldnames. This should be simple enough.

This is what I'm trying to use but it is deleting everything from oldnames.

delete from oldname
select * from newnames

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-26 : 14:35:01

quote:
Originally posted by gogetsome

I want to be able to remove the rows from newname where there are rows in oldnames.

DELETE  N -- N is the alias for table Newname where rows will be deleted (removed)
  FROM Newnames N
  INNER JOIN Oldnames O -- Join on complete matches to Oldnames
    ON N.fname = O.fname AND N.lname = O.lname AND N.age = O.age

gogetsome
Starting Member

10 Posts

Posted - 2005-08-26 : 16:35:34

Hello and thank you for taking the time to help.

I'm rather new to SQL but think that I have created the statment as shown, but it does not seem to work. Is there something that I'm missing here?

delete N
from newnames N
inner join oldnames O
on N.fname = O.fname AND N.lname = O.lname AND N.age = O.age

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-26 : 17:06:05

Try changing it to a SELECT to see what is returned:
SELECT N.*
from newnames N
inner join oldnames O
on N.fname = O.fname AND N.lname = O.lname AND N.age = O.age

gogetsome
Starting Member

10 Posts

Posted - 2005-08-26 : 17:49:16

This returns nothing

select N.*
from newnames N
inner join oldnames O
on N.fname = O.fname AND N.lname = O.lname AND N.age = O.age

This returns all the rows in the newnames table.

select N.*
from newnames N

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-26 : 19:02:50

Well, it looks like you don't have any matches in tables Newnames and Oldnames using all 3 keys... (fname, lname, age)

Posting sample data helps with problems like this.

gogetsome
Starting Member

10 Posts

Posted - 2005-08-29 : 11:04:45

quote:
Originally posted by SamC

Well, it looks like you don't have any matches in tables Newnames and Oldnames using all 3 keys... (fname, lname, age)

Posting sample data helps with problems like this.

You are correct. So, I thought why not combine fname and lname into a combined column to get a unique source (forign key?)
I ran this and it worked:

delete n
from newnames n
inner join oldnames o
on n.combine = o.combine

Thanks for your help

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-29 : 11:08:14

OK.....clue me in here.....HOW does code run without a select statement? Did I miss something here?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-29 : 11:37:53

It's a correlated update/delete....similar in structure to a correlated select.
It's updating records in 1 table, based on existance/values in 1 (or more) other table(s). The placeholder (alias) is key to getting the update/date to work as it identified which table to be actioned.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-29 : 11:54:33

quote:
Originally posted by AndrewMurphy

It's a correlated update/delete....similar in structure to a correlated select.
It's updating records in 1 table, based on existance/values in 1 (or more) other table(s). The placeholder (alias) is key to getting the update/date to work as it identified which table to be actioned.

Thanks A.M.!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources