Saturday, November 6, 2010

Updating entries from a SQL join

Suppose I want to update onl the entries from an INNER JOIN. Well apparently in SQL I can do the following:

UPDATE TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.F1=B.F1 SET A.X = 1, B.Y = 2

According to the MySQL docs, the "table_references" can refer to any type of SELECT syntax we want to use. We can set any value referring to the two entries by giving each of the tables being joined an alias (in the example above, we refer to TABLE1 as 'A' and TABLE as 'B')

http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

1 comment: