Given a SQL statement:

select id from myTable
where id = -1

How do you iterate through each one of the results? I wish to execute a specific operation for every result e.g (in pseudocode):

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

Wouldso would I write a SQL statement to get this done?

N.B. The main reason I do happens because the table whose row I wish to remove has been recommended by other tables. And So I believed that basically remove the rows all that tables that reference this specific row after which remove the row everything is going to be fine.

You don't have to iterate in SQL, you are able to write in a single statement

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


Once you complete the very first remove after that you can remove in the original table

DELETE FROM myTable WHERE id = -1

Another solutions are basically correct. Good SQL code generally requires an alternative way of thinking than how procedural or object-oriented developers are utilized to thinking. SQL is enhanced to do procedures on teams of data. Your DELETE FROM table2 WHERE ... statement is distributed towards the back-finish database, in which the database understands how to most effectively take that statement and carry out the operation over the whole group of results at the same time.

A primary reason for the reason being many large-scale programs possess a front-finish (interface), a server that accepts the consumer demands (e.g.: web server), along with a back finish database server. It might be in efficient for that SQL code being performed on the internet server (where your SQL script may result from) to need to make single demands towards the back finish to remove the items within the table inside a loop. The messages sent on the internet server towards the database server would look something similar to 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 backwards and forwards that will look a lot more like:

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

You should use cursors, but individuals are generally slow and should not be utilized.

You skill is:

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

I generally like to begin with:


To my query in order to check results, after which remove them after i know I am obtaining the right values.

Can perform each 'other' table in a single hit e.g.

  FROM myOtherTable 
               SELECT *
                 FROM myTable AS M1
                WHERE M1.SomeValue = myOtherTable.SomeValue
                      AND M1.ID = -1

You don't have to iterate in sql.

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

Should you ever have to loop in sql you could utilize