How can I update a source SQL Server table with data from another table if the data is not a one-to-one relationship?
For example, the target table - in which the data needs to be entered into, looks like this:
target table has col1 with one row each for 'a' and 'b'
And this is the table from where the data needs to be picked up - source table
this table has 2 rows for record 'a' and 3 records for 'b' in col1
The goal is for the target table to have a total of 5 rows with data for col2, col3, col4 updated from the source table with all the other fields repeated.
I have tried:
UPDATE <table>
SET <columns>
FROM
(SELECT <cols> FROM <sourcetable>
WHERE <clause>)
However, this only picks up the first row for 'a' from the source table and updates the target table. the second row for 'a' is ignored.
