2

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).

Hadi
  • 36,233
  • 13
  • 65
  • 124
TimeToCode
  • 901
  • 2
  • 16
  • 34
  • what do you mean by current record? how do you identify it in `workorders` table? – Vamsi Prabhala Nov 09 '16 at 02:54
  • @vkp can you check again the question please? – TimeToCode Nov 09 '16 at 03:07
  • 1
    Please note that temporary tables (`#WorkOrders`) are usually better than table variables (`@WorkOrders`) because table variables don't have statistics. More detail here https://blogs.msdn.microsoft.com/naga/2015/05/10/sql-server-performance-tuning-table-variable-vs-temporary-tables/ – mendosi Nov 09 '16 at 04:28

1 Answers1

3

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
TimeToCode
  • 901
  • 2
  • 16
  • 34
John Bingham
  • 1,996
  • 1
  • 12
  • 15