updating records from one table using SELECT on another table

Question

updating records from one table using SELECT on another table

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
January 31, 2019 at 1:16 pm

#416668

I'm trying to update a table's column (integer value) with a SELECT on another table both keyed to a common key field "peopleID"
update My_DB..people p set p.certstatusid = select c.statusid from My_DB..certs c
Do I need to add a WHERE clause?
WHERE p.peopleID = c.peopleID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 1

briancampbellmcad - Thursday, January 31, 2019 1:16 PM
I'm trying to update a table's column (integer value) with a SELECT on another table both keyed to a common key field "peopleID"
update My_DB..people p set p.certstatusid = select c.statusid from My_DB..certs c
Do I need to add a WHERE clause?
WHERE p.peopleID = c.peopleID

In SQL Server you can write UPDATE like a select.
For example, if you had
SELECT c.statusid FROM My_DB..people p INNER JOIN My_DB..certs c ON c.peopleID = c.peopleID
You could change it to an UPDATE like this:
UPDATE p SET p.statusid = c.statusid FROM My_DB..people p INNER JOIN My_DB..certs c ON c.peopleID = c.peopleID
For your query you would need WHERE within the SELECT. You would have to make sure the select only returns 1 row. If it didn't return a row the column would be updated to NULL. If it returned more than one row you would get an error. You also need brackets around the select statement.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

Thanks.... the second one using INNER JOIN worked perfectly

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

That format also makes it very convenient to run a SELECT test before the UPDATE, like so:

UPDATE p SET p.statusid = c.statusid --SELECT /*TOP (nn)*/ c.statusid, p.* FROM My_DB..people p INNER JOIN My_DB..certs c ON c.peopleID = c.peopleID

Highlight from the SELECT on to run the test SELECT, then run the whole thing to do the UPDATE.
Btw, note that it's critical to update the alias name, not the original table name.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".