DELETE with conditions from another table
Posted October 3rd, 2008 by WilliamCarrier
Log from IRC server Freenode (IPv6) channel #SQL.
| <me> | how to delete with confitions from another table ? I doubt IN would work ? with inner join ? |
| <me> | ie: delete from t2 where t1.field=# |
| <padan> | what dbms |
| <me> | padan: pure sql preferable, buy mysql - innodb |
| <padan> | well, pure sql won't help you on some dbms :P |
| <padan> | i dont know how ansi sql complient mysql is |
| <padan> | in will work |
| <padan> | i usually end up saying something like where id in (select id from something or another) |
| <me> | if I get the SQL, I'll try to figure for the specific |
| <padan> | well, t-sql (mssql) will go more sql complient and allow a join to another table |
| <padan> | but oracle does not |
| <padan> | so you end up doing correlated subqueries usually |
| <me> | padan: so in like that works ? I hough it absolutely needed in (v1, v2, ...) |
| <padan> | that statement that i said will work all dbms that i work with |
| <padan> | so thats what i do... but again, mysql may implement something much more effective |
| <padan> | i just don't know it since i dont use mysql |
| <me> | padan: it says DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; |
| <me> | it looks like ansi sql though |
| <padan> | that looks like what you can do in sql server |
| <padan> | but if you are going for portability you can't rely on ansi sql |
| <me> | Deleted rows: 4 (Query took 0.0118 sec) woot ! |
| <padan> | :) |
| <padan> | good to know that mysql supports that structure |
| <me> | yup ;) |
| <me> | padan: ty for moral support |
