MySQL syntax

[Chris_Evans]

Hi there,
I would like to do the following:

DELETE FROM amenities WHERE hotels.hotelid != amenities.hotelid

When I do the same in a SELECT statement, it retrieves far too many. Presumably there is another way of doing this?

Thanks,

Chris

[TheDefender]

Maybe I am missing something, or I am overthinking what you are asking... If you want to limit your results, just add a LIMIT clause to your statement.

SELECT * FROM amenities WHERE hotels.hotelid != amenities.hotelid LIMIT 0,10

Where the first integer is the offset (0), and the second integer (10) is the number of returned rows. So in the above case, it returns rows 1-10

In this case below, it returns rows 11-15:
SELECT * FROM amenities WHERE hotels.hotelid != amenities.hotelid LIMIT 10,5

You can read up in the
MySQL Manual in regards to defining attributes in your SELECT statements, including the LIMIT clause.

If this isn't what you meant, then please expand a bit more on what you mean.

[AstroTeg]

Originally posted by Chris_Evans
Hi there,
I would like to do the following:

DELETE FROM amenities WHERE hotels.hotelid != amenities.hotelid

I think you're looking to select/delete all amenities where there's no matching hotel. I could be wrong, but I think this is what you're looking for.

For something like this, you'll want to left join:

SELECT * FROM amenities LEFT JOIN hotels ON hotels.hotelid = amenities.hotelid WHERE hotels.hotelid IS NULL

Its untested. But your goal is to try to link up the existing IDs and find those IDs that do not have a corresponding match. Then doing your delete will make more sense (although I recommend making a backup of the tables you're playing with incase something ugly happens).

[Chris_Evans]

Hi there,
That returns the correct result, now how do I go about deleting the extra fields in amenities that aren't there in hotels?

In other words I need to 'reverse' the way the query works. Rather than opening, it needs to be deleting if that makes sense?

Thanks,

Chris

[AstroTeg]

DELETE FROM amenities LEFT JOIN hotels ON hotels.hotelid = amenities.hotelid WHERE hotels.hotelid IS NULL

That SHOULD do it, but your mileage may vary depending on your database engine you're using. Did I mention you should make a backup of the table you're about to delete from?

[Chris_Evans]

Hi there,
Thanks. I'm always cautious when working with DB's. I made a backup right from the start! 🙂

Thanks for the help,

Chris