[Resolved] Convert mySQL select query to delete?

jalperin

I have two tables: people (with information about people) and schedule (with information about when people are scheduled for a task). The tables can be related by people.id and schedule.people_id.

I want to DELETE all the people who are not scheduled. I know how to write a query that will SELECT all unscheduled people:

SELECT people LEFT JOIN schedule ON people.id = schedule.people_id WHERE schedule.date is NULL

But how can I turn this into a query to delete these records? I need a query that will work with mySQL 3.23, so I can't use any of the new mySQL 4 features.

Any ideas?

--Jeff

vponz

Unfortunately, you can't do this in MySql 3.2 because you can't use subqueries in a delete query. The only way you can achieve this is with a php script that makes a query on 'schedule' and then perform a delete of every row that this query returns.

See the manual for more info

nemonoman

Au contraire, the query as shown will work for delete in 3.2.3. There is no subquery, just a join, which is entirely legal:

DELETE FROM
people
LEFT JOIN schedule
ON people.id = schedule.people_id
WHERE schedule.date IS NULL

nemonoman

A subquery (not available in 3.2.3) has the form

SELECT * from table WHERE table.key IN (SELECT table2.table1key FROM table2)

The select in parens is a subquery.

jalperin

Hmmm. I can't make that Delete query work. I get a mySQL parse error:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join schedule on people.id = schedule.people_id

You were just substituting "DELETE" for the "SELECT *" right?

--Jeff

nemonoman

I WAS WRONG.

jalperin

I just wrote a little php routine which does the deletes one at a time which is fine for my purpose. Thanks.