deleting from two different tables at the same time.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

deleting from two different tables at the same time.

Post by Addos »

Hi,
I’m trying to delete from two separate tables with the following code:

Code: Select all

$deleteSQL = sprintf("DELETE FROM news_letters_ns, word
   WHERE letter_number_ns=barney
    AND letter_number= barney",
Both tables have the same value ‘barney’ and if I run the query with just one table being queried then the delete is carried out it’s just when I try include both tables in the quesy I get the incorrect message:

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 'WHERE letter_number_ns=barney AND letter_number=barney'
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: deleting from two different tables at the same time.

Post by jimthunderbird »

I guess it should be:

Code: Select all

$deleteSQL = "DELETE FROM news_letters_ns, word
   WHERE letter_number_ns='barney'
    AND letter_number= 'barney'"


put '' around barney ?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: deleting from two different tables at the same time.

Post by pickle »

Look up the DELETE syntax in the MySQL manual. You need to put the table names between the DELETE and FROM, and maybe more.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: deleting from two different tables at the same time.

Post by Kieran Huggins »

sounds like what you really want it a transaction
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: deleting from two different tables at the same time.

Post by Addos »

I’m sure there is a better way of doing this however I ended up with the following and it works well.

Thanks for your help.

Code: Select all

$deleteSQLNewsLetter = sprintf("DELETE FROM news_letters_ns
   WHERE letter_number_ns=%s",
            
   $deleteSQLWord = sprintf("DELETE FROM word
    WHERE letter_number=%s",
Post Reply