Skip to content
Advertisement

How do I update the additional fields from 1 table based on the data from another table?

I have 2 temp tables #tempbackfilltable and #temptablelive. The backfill table has more entries than the live table. After cross referencing both tables and getting the additional entries using this code:

I want to update one of the columns (DBTimestamp) in #tempbackfilltable using existing data in #temptablelive. The existing data I would like to update is the min(DBTimestamp) for the day in #temptablelive. I tried running something like this:

However, this gives me 0 rows. I also tried

but this gave me an error saying b.DBTimestamp cannot be bound.

For illustration:
#temptablelive

#tempbackfilltable

As you can see, even though the date is correct in DBTimeStamp, the time is off so I would like to update the time based on the #temptablelive.

Desired output on #tempbackfilltable

Advertisement

Answer

You can use two updates for matching and non-matching rows respectivley. Alternativevly you can use MERGE specifying that unmatched souce gets max DBTimeStamp from the respective set of rows from the source

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement