Greetings:
My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. ############# I need help! ############# Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: --------- John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. <[EMAIL PROTECTED]>: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php