SQL query to update table property from a relation's property of the said table

Question

I'm trying to update the ACCOUNT_GROUP column in a table BILL_INFO_DETAIL from a similar column in table SERVICE_INFO that has a foreign key to BILL_INFO_DETAIL. Each BILL_INFO_DETAIL can have one SERVICE_INFO.

I tried this code, but it didn't select the proper SERVICE_INFO as the WHERE clause didn't work as I intended:

UPDATE BILL_INFO_DETAIL
SET ACCOUNT_CODE = (SELECT TOP (1) si.ACCOUNT_CODE
                    FROM SERVICE_INFO si
                    WHERE si.SERVICE_CODE = SERVICE_CODE);

All items in BILL_INFO_DETAIL's ACCOUNT_CODE got set to the first ACCOUNT_CODE of SERVICE_INFO

Seems this is not that straight forward. Please advise.

Answer 1

Ok. After digging further, I found out how to have Inner Join in Update queries from this answer: https://stackoverflow.com/a/1293347/481656

I modified my query likewise:

UPDATE b
SET b.ACCOUNT_CODE = s.ACCOUNT_CODE
FROM BILL_INFO_DETAIL b
INNER JOIN SERVICE_INFO s ON b.SERVICE_CODE = s.SERVICE_CODE

It worked out nicely.

Rubber Duck.