SQL Delete Problem (mysql)

[TomyB]

Here's on for you, any help would be greatly appreaciated as it is
sending me mad...

I have three tables tbl_photo, tbl_photo_gallery and tbl_gallery.
they are linked through primary and and secondary keys as follows:

tbl_photo.photo_id = tbl_photo_gallery.photo_id
tbl_gallery.gallery_id = tbl_photo_gallery.gallery_id

yeat when i try this delete it fails:

delete from tbl_photo where tbl_photo.photo_id =
tbl_gallery_photo.photo_id and tbl_gallery_photo.gallery_id = (number)

I tried putting this directly into mysql and got the following error:

Unknown table 'tbl_gallery_photo' in where clause

the table definately exists as 'select * from tbl_gallery_photo' works
fine.

If anyone can help I will buy them a pint one day... please?

[Aggro]

TomyB wrote:

delete from tbl_photo where tbl_photo.photo_id =
tbl_gallery_photo.photo_id and tbl_gallery_photo.gallery_id = (number)

You need a syntax similar to this example:

DELETE test FROM test AS t1, test2 WHERE ...

But you should also know that multi-table-delete doesn't work with older
versions of MySQL. You didn't mention what version you are using so
can't say if it works for you, but you can read it for yourself from the
manual. Direct link to correct page below:

http://dev.mysql.com/doc/mysql/en/DELETE.html

[Daniu]

TomyB ??:

Here's on for you, any help would be greatly appreaciated as it is
sending me mad...

I have three tables tbl_photo, tbl_photo_gallery and tbl_gallery.
they are linked through primary and and secondary keys as follows:

tbl_photo.photo_id = tbl_photo_gallery.photo_id
tbl_gallery.gallery_id = tbl_photo_gallery.gallery_id

yeat when i try this delete it fails:

delete from tbl_photo where tbl_photo.photo_id =
tbl_gallery_photo.photo_id and tbl_gallery_photo.gallery_id = (number)

I tried putting this directly into mysql and got the following error:

Unknown table 'tbl_gallery_photo' in where clause
The table name you defined above is 'tbl_photo_gallery '. the table definately exists as 'select * from tbl_gallery_photo' works
fine.

If anyone can help I will buy them a pint one day... please?