SQL Update statements

s_gr

Is there any way to use the sql update command to update rows in tow different tables in the same statement. I have tried various different things but none seem to work. If its not possible, how else couls i do it.

scojones

You can't update 2 tables at once, update one table, then the other.

update table1 set column1 = 'value' WHERE column2 = 'value';
update table2 set column1 = 'value' WHERE column2 = 'value';

db

If you use two update statements you may need to place them inside a BEGIN TRANSACTION ... COMMIT block.
If the columns required to update the second table are in the first table you could use a trigger on the first table to cascade the update.

s_gr

I am still unsure of how to do this, does anyone have an example that i may be able to see how it would work in practice

causal

Read sjones post above again.

The UPDATE command can update only one table.
If you want to UPDATE multiple tables it requires multiple statements - either multiple UPDATE statements; or indirectly using PL/SQL statements e.g. triggers, or stored procedures etc.

If you have more specific info about the tables, columns, values you want to UPDATE, post it for a more specific answer.

causal

fatlog

its not strictly correct to say that you can't do an update across multiple tables. It depends on the DB you are using and the version of it.

for example. you can do updates across multiple tables using MySQL version 4.04 and later like so...

UPDATE table1,table2 SET table1.column1=table2.column2
WHERE table1.column2=table2.column2;

...only reason i'm bringing this up is for the sake of completeness. Chances are the DB/version you are using doesn't support updates acrosss multiple tables.

bonkey

fatlog wrote:

for example. you can do updates across multiple tables using MySQL version 4.04 and later like so...

UPDATE table1,table2 SET table1.column1=table2.column2
WHERE table1.column2=table2.column2;

I'm not an expert on MySql, but the sample you show is only updating one table. Its referencing two tables, but only updating one. Can you update both in the same statement?

Can you say, for example:

UPDATE table1,table2
SET table1.column1= foo, table2.column1 = bar
WHERE ...

MSSQL, for example, will allow you to update Views which are based on multiple tables, but with certain restrictions...one of which is that only columns from a single table are updated in any one statement.

fatlog

yep you can update two (or more) tables

like...

update table1, table2 set table1.column1=table2.column1,
table2.column2=table1.column2 where table1.column3=table2.column4

causal

Multiple tables in an UPDATE statement isn't part of the SQL2 standard; BUT
fatlog you may very well be right (I'm not saying you're wrong) but I'm not convinced about the UPDATE in MySQL - mainly because in the manual the damned example they give doesn't actually update multiple tables, and it says "perform UPDATE operations that cover multiple tables" - I expected to see "update multiple tables".

Can someone actually do a quick test to see if indeed multiple tables can be updated in a single UPDATE command in MySQL - in the interest of learning for all

From the MySQL 4 manual:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

or

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Note: you can not use ORDER BY or LIMIT with multi-table UPDATE.

causal