CODEWITHSUNDEEP
pythonmysql
Josh Usre
Josh Usre

Reputation: 683

Check if any elements of list match row in MySQL table

My first query:

sq1 = """SELECT site_id
FROM site_owner
WHERE user_id = %s"""

returns a list of integers ("site-ids") that belong to user_id.

In my second query, I need to see if any of the integers in the list match a row in a column in another table called "sites", and if so delete that row.

I'm a SQL noob and don't know if it's possible to iterate over a list in this fashion through a query. Thanks for you time.

EDIT:

I understand there's a way to delete from a select query. My unsuccessful attempt is as follows:

sq2 = """DELETE FROM site WHERE name = %s
AND site_id IN (
    SELECT site_id FROM site_owner WHERE user_id = %s )"""

Is this the right track?

Upvotes: 1

Views: 459

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

You can't iterate in queries, but you can in stored procedures. However, you shouldn't need to in this case. delete results of a select with a JOIN

Upvotes: 1

Related Questions