TimeToCode TimeToCode - 23 days ago 11
SQL Question

Delete a number of records given by the number of records in a table

I have a table variable called

@workorders
, this table are filled by a query:

DECLARE @workorders table
(id_workorder VARCHAR(100));
INSERT INTO @workorders SELECT DISTINCT(id_workorder) FROM CampaingControl WHERE id_campaing = @id;
--get each record of the table @workorders and delete of another table


Then, what I want is to go through each record of the table
@workorders
and delete a record with the value of the current record.

The table where I want to delete the records is called
WorkOrders
, this table have a column called
id_workorder
, for example:

Assuming the table variable
@workorders
have 3 records:


  • OT-001-16

  • OT-002-16

  • OT-005-16



I need to implement a kind of while loop, to get each record on the table
@workorders
and for each record delete on the table
WorkOrders
where
id_workorder
=
@workorders (id_workorder VARCHAR(100))(Current record)
.

Answer

Can you not simply INNER JOIN the @workorders table in your delete statement?

DELETE w 
FROM workorders w INNER JOIN @workorders tw ON w.id_workorder = tw.id_workorder
Comments