January 31, 2019 at 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
January 31, 2019 at 1:30 pm
briancampbellmcad - Thursday, January 31, 2019 1:16 PMI'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.
January 31, 2019 at 1:43 pm
Thanks.... the second one using INNER JOIN worked perfectly
January 31, 2019 at 1:48 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply