Link to home
Start Free TrialLog in
Avatar of ITBenelux
ITBeneluxFlag for Belgium

asked on

SQL Update


I don't understand why my update statement is not working

I want to update table tbluser with values of tblDump, comparing the UserID's.  I put a unique index on the userID field in both tables, So I'm 100% sure there are no duplicate values.  yet.. when running the update statement. I get back 'Subquery returned more than 1 value.' ?? which should be impossible. because there is always one value for one ID ?

thx for helping me out on this one.
USE users
UPDATE tbluser
SET PartnerBirthday = (SELECT PBirthday
FROM users.dbo.tblDump
WHERE users.dbo.tblDump.userid  = users.dbo.tbluser.userid)

Open in new window

Avatar of Pratima
Flag of India image

try this

USE users
UPDATE users.dbo.tbluser
SET PartnerBirthday = PBirthday
From users.dbo.tblDump
WHERE users.dbo.tblDump.userid  = users.dbo.tbluser.userid
But there is nowhere you are constraining the results of the sub query, so it will return all user records.

SET u.PartnerBirthday = d.PBirthday
FROM users.dbo.tblDump d
JOIN users.dbo.tbluser u
  ON u.userid  = d.userid

Open in new window

Will update all records in tblUser where there is a record in tblDump.
Avatar of BourbonKid
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
USE users
UPDATE users.dbo.tbluser
SET PartnerBirthday = PBirthday
From users.dbo.tbluser  inner join  users.dbo.tblDump
 on users.dbo.tblDump.userid  = users.dbo.tbluser.userid
UPDATE table2  SET table2.col1 = table1.col1,  table2.col2 = table1.col2
FROM table1, table2  
Avatar of ITBenelux


It seems this answer helped me out. In the end I was wrong, running the query showed me that there are duplicated values. however I was sure to put the index correctly.  So it's back to the drawing table. but this helped me on the right track.