SQL Update From Where Query

Question

I have 2 tables with information in them. I need to update the SelfServiceUserName column in table A_CLIENT with the value from the SubstVarValue column of the A_DEV_SUBSTVAR_VALUE table when the ClientUID and DeviceID match and the SubstVarName from the A_DEV_SUBSTVAR_VALUE table = samaccount name. Here is the query I've tried to run but I keep getting errors:

UPDATE A_CLIENT
SET SelfServiceUserName = (SELECT SubstVarValue
  FROM A_DEV_SUBSTVAR_VALUE
  WHERE A_DEV_SUBSTVAR_VALUE.SubstVarName = 'samaccountname')
  WHERE A_CLIENT.ClientUID = A_DEV_SUBSTVAR_VALUE.DeviceID

possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Jen R, Apr 22 '15 at 20:05

score 1 · Answer 1 · answered Apr 22 '15 at 20:19

I always write a join between the two tables first to get the rows I want side by side and make sure I have the JOIN clause correct.

SELECT p.ProductID, p.ProductName, p.Price AS OldPrice, n.Price as NewPrice
FROM Products as p
JOIN NewPrices as n on p.ProductID = n.ProductID

Once I have done that it's easy to change it into an update statement by replacing the SELECT clause with an UPDATE and SET:

UPDATE p
SET Price = n.Price
FROM Products as p
JOIN NewPrices as n on p.ProductID = n.ProductID

Note you don't alias the Price on the left side of the SET clause, because it is necessarily from the p (Product) table, so there is no ambiguity. You must still alias the Price on the right of the equals because it could be the field coming from either the p (Product) or n (NewPrice) table.

You could also use a CTE (Common Table Expression) if your SQL engine supports it:

WITH x AS (
SELECT p.ProductID, p.ProductName, p.Price AS OldPrice, n.Price as NewPrice
FROM Products as p
JOIN NewPrices as n on p.ProductID = n.ProductID
)
UPDATE x set OldPrice = NewPrice

score 0 · Accepted Answer · answered Apr 22 '15 at 20:03

0

Try something like

update a_client c
inner join  a_dev_substvar_value d on
  c.clientuid = d.deviceid
set 
  c.selfserviceusername = d.substvarvalue
where 
  d.substvarname = 'samaccountname';

Note, you should try avoid writing select statements in your were clause because it is run for ever row returned. This can be a big performance hit.

That should work.

answered Apr 22 '15 at 20:03

Ryan-Neal Mes

6,003
7
52
77

Another point is looking at your join clause should `clientuid = deviceid`? Sounds like there could be something incorrect there. – Ryan-Neal Mes Apr 22 '15 at 20:05
Thanks. You got me on the right track. I just had to make some changes to the INNER JOIN syntax. – James Apr 22 '15 at 22:10

SQL Update From Where Query

2 Answers2