Update multiple tables in a single update statement with left join

Question

I realise what I'm asking for may be impossible. I want to perform an UPDATE on two separate tables based on a LEFT JOIN and a WHERE clause. This is my attempt:

UPDATE  PERIODDATES as pd, periods2 as p2
SET     pd.[PERIODCODE] = @PERIODCODE,
     p2.[USERCODE] = @USERCODE
             left join periods2 AS p2
    ON  pdates.schemeid = p2.schemeid AND

WHERE [SCHEMEID] = @SCHEMEID

Is this possible?

Answer 1

This is not possible. You can only update one table in one statement. You will have to split this out in to two statements.

Edit:

Updating two tables in one statement is actually possible but will need to create a View using a UNION that contains both the tables you want to update. You can then update the View which will then update the underlying tables.

It seems like a bit of hack to me but it will work.