INSERT values from temp table conditionally into source table

Question

I have a big INSERT/SELECT statement and I capture two fields via OUTPUT like so:

DECLARE @temp TABLE (
    spam INT,
    eggs INT
);
    
INSERT INTO dest_table ( foo, bar, baz )
OUTPUT
    inserted.foo,
    inserted.bar INTO @temp(spam, eggs)
SELECT
    foo,
    bar,
    baz
FROM
    source_table 
WHERE
    eggs IS NULL;

I end up with a big mapping table like this:

spam | eggs
-----+------
 1   | 100
 2   | 200
 3   | 300
 4   | 400

Now what I need to do is "backfill" this data into the original source table.

I need something like:

UPDATE
    source_table
SET
    eggs = ...
WHERE
    eggs IN
    (
        SELECT eggs FROM @temp WHERE spam = source_table.id
    )

Answer 1

Your UPDATE statement is almost there, but you want this:

UPDATE
    s
SET
    eggs = t.eggs
FROM
    source_table AS s
    INNER JOIN @temp AS t ON s.id = t.spam
WHERE
    s.eggs IS NULL;

• Using INNER JOIN with @temp will filter source_table so only relevant rows will be updated, and also means it maps each source_table row to a row in @temp.
• More examples here: How do I UPDATE from a SELECT in SQL Server?