How can I update a source SQL table with data from another table if the data is not a one to one relationship?
e.g. target table – in which the data needs to be entered into
target table has col1 with one row each for ‘a’ and ‘b’

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.