I wanted to write a query to delete from x table and y table where object id="123" selecting object id from z table for the given name in 'z' table.How to write a sql query for this ?
Please let me know .
You can't delete from two tables at once. You need two delete statements.
DELETE x WHERE object_id IN (SELECT object_id FROM z WHERE name = 'whatever') DELETE y WHERE object_id IN (SELECT object_id FROM z WHERE name = 'whatever')
I've used IN just in case you have multiple objects with the same name. You may want to change that behaviour to suit your needs.
delete t1,t2 from x t1 inner join y t2 on t1.id=t2.id where t1.id in (SELECT object_id FROM z WHERE name = 'YourName');
Tested on Mysql.
Delete from different tables in a single batch (pool) is a synchronus process, ie it executes one by one, so you need to write 2 different queries to delete any data according to your requirement.
Assuming there is only 1 data for the given name DELETE X WHERE object_id = object_id FROM Z WHERE name = 'name' DELETE y WHERE object_id = object_id FROM z WHERE name ='name'
this gives better performance then 'IN'
Well offcourse if you have multiple records for a single name then you have to use 'IN'