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
