How to use update command in sql server.

Question

Hey everyone,
I am trying to update a record of a table depending on some condition. But as I am not much into sql queries, I need some help.
The problem statement is:
There are 4 columns in my table:

As you could see that InvoiceRecipientId column is having NULL.
I want to update this column (InvoiceRecipientId) and set it's value depending upon the values of AddressId and InvoiceRecipientAddressId.
Condition is that if in current record, AddressId is NOT EQUALS TO InvoiceRecipientAddressId then we have to pick the Id of the record whose AddressId is equal to the InvoiceRecipientAddressId of current record and put that Id into the InvoiceRecipientId column of current record.

Desired data in table after running the query is in below snapshot:

I used the below query in sql and it's working but not working in sql server because we can't use as with update I think.

update person as x set x.InvoiceRecipientId = (select Id from person as p where p.AddressId = x.InvoiceRecipientAddressId) where x.InvoiceRecipientAddressId = (select p.AddressId from person p where p.AddressId = InvoiceRecipientAddressId)

Please help.

Answer 1

Check this adjustment:

update p  
set InvoiceRecipientId = (select top(1) Id from Person where AddressId = InvoiceRecipientAddressId and AddressId = p.InvoiceRecipientAddressId order by Id)  
from Person p  
where AddressId != InvoiceRecipientAddressId  

I think that there are more solutions.

Answer 2

Hi @Samuel
Please check this:

UPDATE A  
SET InvoiceRecipientId = B.Id  
FROM person A   
JOIN (SELECT Id,InvoiceRecipientAddressId FROM person  WHERE AddressId=InvoiceRecipientAddressId) B   
  ON A.InvoiceRecipientAddressId=B.InvoiceRecipientAddressId  
WHERE A.AddressId <>A.InvoiceRecipientAddressId  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.