Page 1 of 1

deleting from two different tables at the same time.

Posted: Mon Jan 14, 2008 11:31 am
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'

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

Posted: Mon Jan 14, 2008 4:39 pm
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 ?

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

Posted: Mon Jan 14, 2008 5:16 pm
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.

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

Posted: Tue Jan 15, 2008 2:43 am
by Kieran Huggins
sounds like what you really want it a transaction

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

Posted: Tue Jan 15, 2008 5:33 am
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",