Update multiple tables in a single update statemen

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.

Answer 2

Reviving a an old question here, but... you can simulate updating multiple tables in a single query via two UPDATE queries wrapped in a transaction. This way you don't have to get messy using up-datable views and no changes will be made unless both tables update successfully:

BEGIN TRANSACTION

UPDATE PERIODDATES AS pd
SET pd.PeriodCode = @PeriodCode
WHERE [SCHEMEID] = @SCHEMEID;

UPDATE periods2 AS pd2
SET pd2.[USERCODE] = @USERCODE
WHERE [SCHEMEID] = @SCHEMEID;

COMMIT;

for your inserts, you can use either @@IDENTITY, or OUTPUT INTO to grab the id of the new record to use in the second table, ie:

DECLARE @ID int;

BEGIN TRANSACTION

INSERT INTO PERIODDATES( PeriodCode ) AS pd
VALUES ( @PeriodCode )
WHERE [SCHEMEID] = @SCHEMEID;

SELECT @ID = @@IDENTITY;

INSERT INTO periods2( [SCHEMEID], [USERCODE] ) AS pd2
VALUES ( @ID, @USERCODE );

COMMIT;