CODEWITHSUNDEEP
mysql
Mint.K
Mint.K

Reputation: 899

MySQL DELETE based on SELECT

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.

Upvotes: 0

Views: 86

Answers (1)

Barmar
Barmar

Reputation: 780673

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

Upvotes: 4

Related Questions