6

I'm trying to run this MYSQL command:

DELETE FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL

It is returning this 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 'h LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id WHERE uh.user_hotel_id I' at line 1

The command seems self-explanatory, so not sure what I'm getting wrong. Any ideas?

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
zeckdude
  • 15,877
  • 43
  • 139
  • 187

6 Answers6

2

you are missing table_name those you want to delete 

DELETE h FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL
Ankit Agrawal
  • 2,426
  • 1
  • 13
  • 27
  • I thought it may be something like that, but I was checking out this article and it just had it as the way I wrote it. Do you know why they don't mention it the way you are? http://www.techonthenet.com/mysql/delete.php – zeckdude Apr 28 '16 at 06:51
  • in this article they are performing action on single table ,while in your case you have two table .hence you have to mention table_name that you want to delete :-) – Ankit Agrawal Apr 28 '16 at 06:53
  • Thanks Jordan that makes sense – zeckdude Apr 28 '16 at 06:54
  • 1
    @zeckdude:- When you are deleting the records using JOIN then you need to mention the table name from which you want to delete else it will create an ambiguity for the MYSQL engine. In the link which you provided they are using a single table hence you dont need to specify the name of the table explicitly as it is implictly understood by the engine. – Rahul Tripathi Apr 28 '16 at 06:54
  • Mark this as accept answer ..so it will be helpful to others – Ankit Agrawal Apr 28 '16 at 06:55
1

You need to provide the table name which you want to delete like

DELETE h FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
1
DELETE h FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL
Osama Aftab
  • 1,161
  • 9
  • 15
1

# what to delete is missing . Delete from table work only if no join 

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition
shalini
  • 1,291
  • 11
  • 13
1

You need to specify table name on which you are going to apply DELETE in case of JOIN. See this link.

You need to write MySQl query like below:

DELETE h,uh FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL
Community
  • 1
  • 1
Mohammad Sayeed
  • 2,025
  • 1
  • 16
  • 27
0

Query:

DELETE column_name(which you want to delete) FROM hotel h
LEFT JOIN user_hotel uh ON h.hotel_id = uh.hotel_id
WHERE uh.user_hotel_id IS NULL
Musakkhir Sayyed
  • 7,012
  • 13
  • 42
  • 65
rutvee sojitra
  • 1