Populate NULL component_ids from related tables

Needed to populate a new column in one table from a semi-related existing table, based on a value in another related table. I do this once in a blue moon and needed a refresher. Thanks, Pinal Dave, for your post with the syntax!

UPDATE [dbo].[ACA_swprg_software_parts]
SET [ACA_swprg_software_parts].mes_component_id=cpn.component_id
FROM [dbo].[ACA_swprg_software_parts] sp
INNER JOIN [dbo].[ACA_swprg_programming_steps] prgstep ON prgstep.[programming_step_id] = sp.[programming_step_id]
INNER JOIN qs_components cpn ON cpn.product_id=prgstep.[mes_product_id] AND cpn.part_number = sp.[part_number]
WHERE sp.mes_component_id IS NULL

Ref: https://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement/

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *