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 .

Answers


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.


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.