CODEWITHSUNDEEP
sql-update
James
James

Reputation: 3

SQL Update From Where Query

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

Upvotes: 0

Views: 483

Answers (2)

Joseph Gagliardo
Joseph Gagliardo

Reputation: 783

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

Upvotes: 1

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

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.

Upvotes: 0

Related Questions