query formation

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.

  object_id IN (SELECT object_id FROM z WHERE name = 'whatever')

  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.

Try This..

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'

Need Your Help

Stored procedure problem- Entityspaces

.net stored-procedures entityspaces

I intend to execute a stored procedure using enityspaces and wrote the following method in Custom file.