How can I iterate through a list of rows returned from a select statement in SQL?

Question

Given a SQL statement:

select id from myTable
where id = -1

How do I iterate through each of the results? I want to perform a specific operation for each result e.g (in pseudocode):

foreach (result from my statment)
{
  delete all rows from myOtherTable that has a value of result;
}

How would I write a SQL statement to do this?

N.B. The reason I want to do this is because the table whose row I want to delete is being referenced by other tables. So I thought that if I delete the rows from all that tables that reference this particular row and then delete the row everything will be fine.

Answer 1

You can use cursors, but those are by and large slow and shouldn't be used.

What you can do is this:

DELETE FROM a
FROM
    myOtherTable a
    INNER JOIN myTable b ON
        a.value = b.value
        AND b.id = -1

I generally like to start with:

SELECT a.*
--DELETE FROM a

To my query so I can check results, and then delete them when I know I'm getting the right values.

Answer 2

You don't need to iterate in SQL, you can write in one statement

DELETE FROM myOtherTable
WHERE myTableId IN (SELECT id FROM myTable WHERE id = -1)

EDIT:

After you complete the first delete you can then delete from the original table

DELETE FROM myTable WHERE id = -1

Answer 3

Can do each 'other' table in one hit e.g.

DELETE 
  FROM myOtherTable 
 WHERE EXISTS (
               SELECT *
                 FROM myTable AS M1
                WHERE M1.SomeValue = myOtherTable.SomeValue
                      AND M1.ID = -1
              );

Answer 4

You don't need to iterate in sql.

DELETE FROM table2
WHERE Id IN (SELECT id FROM table1 WHERE id = -1)

If you ever need to loop in sql you could use

while

Answer 5

The other answers are essentially correct. Good SQL code generally requires a different way of thinking than how procedural or object-oriented programmers are used to thinking. SQL is optimized to perform operations on sets of data. Your DELETE FROM table2 WHERE ... statement is sent to the back-end database, where the database knows how to most efficiently take that statement and perform the operation across the whole set of results at once.

One of the reasons for this is because many large-scale applications have a front-end (user interface), a server that accepts the user requests (e.g.: web server), and a back end database server. It would be in efficient for the SQL code being executed on the web server (where your SQL script may originate from) to have to make single requests to the back end to delete each item in the table in a loop. The messages sent from the web server to the database server would look something like this (pseudo-code messages):

web server ->    Find all elements matching criteria  -> database
web server <-     (return of matching elements)       <- database
web server ->    Delete element[0] from myTable       -> database
web server <-     (delete result was ok)              <- database
web server ->    Delete element[1] from myTable       -> database
web server <-     (delete result was ok)              <- database
....

Whereas, the SQL approach would produce messages back and forth that would look more like:

web server ->    Find all elements matching criteria  -> database
                    and delete them.
web server <-     (delete result was ok)              <- database