Update 2 tables in one statement (MS SQL)

Question

Possible Duplicate:
How to update two tables in one statement in SQL Server 2005?

I have an update statement. It Updates one field but inner joins on another table to complete the where clause. I am using MS SQL.

I am now trying to update a field on the joined table but cannot seem to do it. I have read that you can only update one table at a time. Is this true? Is there a way I can get around this?

Here is my statement

update tbl_calendardatebox
set 
tbl_calendardatebox.HeaderBgColour = @value,
tbl_calendarobjects.Saved = '0'

from tbl_calendardatebox db
inner join tbl_calendarobjects o on
db.ObjectId = o.Id

where o.PageId = @page
and o.GroupField = @group and o.GroupField <> '-1'
and o.Visible = '1'
and o.CanUserEdit = '1'
and db.HeaderBgColour <> @value

So the two tables are tbl_calendardatebox and tbl_calendarobjects. I get the error message on tbl_calendarobjects.Saved = '0' - The multi-part identifier "tbl_calendarobjects.Saved" could not be bound.

Any help will be appreciated.

Answer 1

Updating multiple tables at the same time is not possible. Following is the abstract from MSDN which clearly shows

{} represents mandatory field

[] represents optional fields

[...n] represents 0 or more

See this

UPDATE 
        { 
         table_name WITH ( < table_hint_limited > [ ...n ] ) 
         | view_name 
         | rowset_function_limited 
        } 
        SET 
        { column_name = { expression | DEFAULT | NULL } 
        | @variable = expression 
        | @variable = column = expression } [ ,...n ] 

    { { [ FROM { < table_source > } [ ,...n ] ] 

        [ WHERE 
            < search_condition > ] } 
        | 
        [ WHERE CURRENT OF 
        { { [ GLOBAL ] cursor_name } | cursor_variable_name } 
        ] } 
        [ OPTION ( < query_hint > [ ,...n ] ) ] 

Following section cannot contain any join. "{ }" represents mandatory field which can contain View Name or table name but not join.

{ 
 table_name WITH ( < table_hint_limited > [ ...n ] ) 
 | view_name 
 | rowset_function_limited 
} 

Answer 2

Syntax fix to begin with, might solve the problem as well, untested!

update db
set 
db.HeaderBgColour = @value,
o.Saved = '0'

from tbl_calendardatebox db
inner join tbl_calendarobjects o on
db.ObjectId = o.Id

where o.PageId = @page
and o.GroupField = @group and o.GroupField <> '-1'
and o.Visible = '1'
and o.CanUserEdit = '1'
and db.HeaderBgColour <> @value

Actual answer found on stackoverflow: How to update two tables in one statement in SQL Server 2005?

This is NOT possible Sadly.

Answer 3

Hope this example (taken from here) can help you:

UPDATE a 
INNER JOIN b USING (id) 
SET a.firstname='Pekka', a.lastname='Kuronen', 
b.companyname='Suomi Oy',companyaddress='Mannerheimtie 123, Helsinki Suomi' 
WHERE a.id=1; 

You query (I cannot test it, sorry) could be:

UPDATE tbl_calendardatebox cdb
  INNER JOIN tbl_calendarobjects co
    ON cdb.ObjectId = co.Id
SET cdb.HeaderBgColour = @value
  , co.Saved = '0'
WHERE co.PageId = @page
AND co.GroupField = @group 
AND co.GroupField <> '-1'
AND co.Visible = '1'
AND co.CanUserEdit = '1'
AND cdb.HeaderBgColour <> @value

Answer 4

Like Shantanu told, it isn't possible to update multiple tables. Anyway, if your envirornment allow it, you could walk on procedure way. It's safe and atomic. Remember, on catch an error, rollback always.