Bob Lockie wrote:
What I really want was
mysql> update records set records.prio=2 where records.in=(select records.id from records, audit_log, audit_log_records where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null);
but that gives a syntax error and I don't think I can do a select inside an update. :-(
Subqueries require mysql 4.1 or higher.
mysql> update records set records.prio=2 where audit_log.tracker_id=audit_log_records.tracker_id and records.id=audit_log_records.id and audit_log.operation='D' and audit_log.completed is null;
ERROR 1109: Unknown table 'audit_log' in where clause
You have to name all the tables you need in the UPDATE clause before you can use them in the WHERE clause. So, you need
UPDATE records, auditlog, audit_log_records SET records.prio=2 WHERE audit_log.tracker_id=audit_log_records.tracker_id AND records.id=audit_log_records.id AND audit_log.operation='D' AND audit_log.completed is null;
This is a multiple-table update, which is supported starting with mysql 4.0.4. Prior to that, you can't do this with one statement. See the manual <http://dev.mysql.com/doc/mysql/en/UPDATE.html>.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
