no update and too many inserts ***

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

no update and too many inserts ***

Author

Topic

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-30 : 14:24:12

HI,
I have code that should update if the USERID exists but insert a row if the USERID does not exist. But what happens is that all get inserted with the updated information which creates duplicates.

My question is, how can i rearrage things to get the right outcome? I tried putting the Insert within the WHERE NOT EXISTS etc. but i'm failing.

update ut
set UserName = uf.UserName,
SSN = uf.SSN,
LastName = uf.LastName
from Usersbackupoct2309 as ut
join ULchangefromtwo as uf
on uf.UserName = ut.UserName AND
uf.UserName = ut.SSN
INSERT INTO Usersbackupoct2309 (UserName, LastName, FirstName, SSN)
SELECT UserName, LastName, FirstName, SSN
FROM ULchangefromtwo o
WHERE NOT EXISTS (
SELECT *
FROM Usersbackupoct2309 i
WHERE o.UserName = i.UserName
AND o.UserName = i.SSN )

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-30 : 14:36:42

I don't see UserID anywhere...but assuming that you can only have one record per SSN, you can update/insert the others like this:


INSERT	Usersbackupoct2309 (UserName, LastName, FirstName, SSN) 
SELECT 	UserName, LastName, FirstName, SSN 
FROM 	ULchangefromtwo o
LEFT JOIN
	Usersbackupoct2309 ut
On	ut.ssn = o.ssn
WHERE	ut.ssn IS NULL;

update	ut
set 	UserName = uf.UserName,
	LastName = uf.LastName 
from 	Usersbackupoct2309 as ut
join 	ULchangefromtwo as uf
on 	uf.UserName = ut.SSN;

What version of SQL Server? Because in 2008 it's even easier

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-30 : 14:55:21

I'm sorry i meant to say UserName not USERID.

I'm using SQL Server 2000. I know we are behind the times.

The only thing wierd about using the new code is i get "Ambiguous column name" for the column names of the table (UserName, LastName, FirstName, SSN) but thats what they are called! Should i define them another way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-30 : 15:11:23

They are ambiguous because they are present in another table in your query. You need to prefix them with the tablename or use an alias.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-30 : 15:56:49

thanks, line 2 is what needed the o.column name....(shaking head)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources