The key part of the UPDATE documentation for me was:
The table_references clause lists the tables involved in the join. Its syntax is
described in Section 13.2.7.1, "JOIN Syntax".
Basically you can take almost any SELECT statement with JOINs and convert it to an UPDATE. There are certain limitations as stated
in the documentation. Just think of the SELECT statement as your filter for the UPDATE, you're just adding SET...
Any fields that would normally be accessible in the SELECT statement are eligible to be in the SET part of the UPDATE statement. You
can also reference multiple table and only update a field in one of them.
UPDATE [select syntax]
SET x.f1=b, y.f1=a
WHERE ...
So if you wanted to update all records in table x that had no related records
in y, you can do something like this:
UPDATE x LEFT JOIN y ON x.id=y.id
SET x.itemsExist="N"
WHERE y.id IS NULL
Or the opposite with a multi-table update:
UPDATE x,y
SET x.YitemsExist="Y", y.XitemsExist="Y"
WHERE x.id=y.id
----- Original Message -----
From: "Chris White" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, January 10, 2007 12:55 PM
Subject: Multiple table updates (Was: Does Update allow for aliases)
Reading the noted previous thread, I was curious as to updating multiple
tables. I read the MySQL docs, which mentions that you can do it:
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] /|table_references|/
SET /|col_name1|/=/|expr1|/ [, /|col_name2|/=/|expr2|/ ...]
[WHERE /|where_condition|/]
However, I didn't see any sort of example for achieving this (that
wasn't somewhat complicated). Does anyone have a base example
(preferably with table structure) that can show how this works?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
