From: Michael Widenius Date: March 19 1999 10:47am Subject: SQL Question??: Using Join in Update List-Archive: http://lists.mysql.com/mysql/583 Message-Id: <14066.10648.978149.392954@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Brandon" == Brandon Shuey writes: Brandon> I am running mySQL under NT4.0. Brandon> I am trying a simple update statement that does a left join and updates on null values. The exact same join works in a select Brandon> statement but will not work in a update statement. I get the following error: Brandon> ERROR 1064 at line1: parse error near 'LEFT JOIN table2 ON table1.ID=table2.Field' Brandon> This is the select statement that works correctly: Brandon> SELECT table1.* FROM table1 Brandon> LEFT JOIN table2 ON(table1.ID = table2.Field) Brandon> WHERE (((table2.Field) is NULL)) Brandon> Here is the update that doesn't work: Brandon> UPDATE table1 Brandon> LEFT JOIN table2 ON table1.ID = table2.Field Brandon> SET table1.ID = "Invalid" Brandon> WHERE (((table2.Field) Is Null)); Brandon> The documentation does not address joins in Update queries so I do believe this is a valid question. Hi! The UPDATE clause is not allowed in ANSI SQL (and not in MySQL, even if we have it on the TODO). crash-me (http://www.mysql.com/crash-me-choose.htmy) tests the above clause with 14 common SQL servers, and only Access supports the above. Regards, Monty