Menu
I need to delete some rows from a table, based on a mixed where statement from two tables.
I tried this:
delete from tblI t1, tblS t2
where t2.rcode = 'ALA' and t1.sid > 5
but I get a syntax error. Please help me figure this out
Changed it to JOINS:
delete from tblI
inner join tblS
on tblI.sourceid = tblS.sourceid
where tblS.rcode = 'ALA' and tblI.sourceid > 5
but something is still wrong, please help.
389
You cannot DELETE from multiple tables with a single expression in SQL 2005 - or any other standard SQL for that matter. Access is the exception here. The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger .
MySQL DELETE JOIN with INNER JOIN MySQL also allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table. Notice that you put table names T1 and T2 between the DELETE and FROM keywords.
Example - Using EXISTS with the DELETE Statement You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
DELETE Syntax Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
You have to tell it which table to delete from.
delete t1
from tblI t1
join tblS t2 on t1.sid = t2.sid
where t2.rcode = 'ALA'
and t1.sid > 5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
