SSIS - Update flag of selected rows from more than one table

Question

Hi

I have a SSIS package that copies data from table A to table B and sets a flag in table A so that the same data is not copied subsequently. This works great by using the following as the SQL command text on the ADO Net Source object:

update transfer
set ProcessDateTimeStamp = GetDate(), LastUpdatedBy = 'legacy processed'
output inserted.*
where LastUpdatedBy = 'legacy'
and ProcessDateTimeStamp is not null

The problem I have is that I need to run a similar data copy but from two sources table, joined on a primary / foreign key - select from table A join table B update flag in table A.

I don't think I can use the technique above because I don't know where I'd put the join!

Is there another way around this problem?

Thanks

Rob.

Answer 1

You can use a join in an update statement.

update m 
set ProcessDateTimeStamp = GetDate(), 
    LastUpdatedBy = 'legacy processed', 
    somefield = t.someotherfield
    output inserted.* 
from transfer t
join mytable m 
    on t.id = m.id
where m.LastUpdatedBy = 'legacy' 
    and m.ProcessDateTimeStamp is null 
    and t.ProcessDateTimeStamp is not null 

The key is to not alias the fields on the left side of the set but to alias everything else. And use the table alias for the table you are updating after the update key word so it knows which table of the join to update.