MySQL DELETE based on SELECT

Question

I am learning SQL. Using MySQL, I am working on a very simple calendar application. SELECT statement below returns a relation with the schedule ID. And I'd like to delete it using the DELETE statement. So I am trying to delete a specific schedule only in schedule table. But I am getting a syntax error.

ERROR 1064 (42000): You have an error in your SQL syntax;

SELECT s.id 
FROM schedule s, meetings m 
WHERE m.date = s.date AND m.time = s.time AND m.id = 1;
// returns 5

DELETE
FROM schedule s, meetings m 
WHERE m.date = s.date AND m.time = s.time AND m.id = 1;  
// trying to delete the row with id=5 but syntax error

Please help.

if you want to delete from 2 table you should to write 2 query, if you want to delete from one table with this condition(as shown above in your question) you should to join tables together and delete from one you want...now tell us you want to delete from which table dude — Farrokh
– Farrokh, Commented Mar 4, 2018 at 9:04

Barmar · Accepted Answer · 2018-03-04 09:05:50Z

4

See the multi-table syntax in the DELETE documentation. The main point is that you have to specify which table(s) you're deleting from after the DELETE keyword:

DELETE s
FROM schedule AS s
JOIN meetings AS m ON m.date = s.date AND m.time = s.time
WHERE m.id = 1

answered Mar 4, 2018 at 9:05

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL DELETE based on SELECT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related