Deleting from multiple tables gives me 'column is ambiguous' error

Databases
1

Hello, I’ve got this delete statement:

DELETE actor.*, edition.*, country2title.*, director2title.*, lang2title.*, producer2title.*, studio2title.*, sub2title.*, writer2title.* FROM actor, edition, country2title, director2title, lang2title, producer2title, studio2title, sub2title, writer2title WHERE titleId = 9

The error I get is:

Column ‘titleId’ in where clause is ambiguous

Now, I understand the error, Mysql is confused as to which titleId to use to delete. All the tables have a titleId column and their value will always be the same for each title.

So the question is how do I get around this problem without having to do individual delete queries for each table?

2

Damn, my prepared statement solution has a problem in that mysql gives me an error because PDO will escape the name of the table and put it in single quotes. Mysql doesn’t like that at all.

Anyone have any idea how to get around that?

3

i would advise you to run separate queries anyway

otherwise you need to join all those tables properly, and then there is the issue of missing values, where if one of the joined tables doesn’t have a row for that value, then the join will fail for that value, and none of the other rows which did have a value will get deleted

4

You need to make it unambiguous by doing

WHERE actor.titleId = 9 AND othertable.titleId = 9 AND anothertable.titleId = 9

But, Rudy is correct. Separate would be best. You might get unexpected results at best.

5

Yes, I think you’re right :slight_smile: however I discovered it’s impossible to overcome this table issue.

thanks again.

6

I tried that and it did not work, it did not delete anything.

7

Oh, ok :frowning:

I thought this way would be faster.

However, it sounds like a good occasion to use PDO prepared statements. However I’m not sure how the syntax would be. I create 1 prepare statement and then just run the subsquent execute statement with the array containing the table name right?

$con->prepare("DELETE FROM :table WHERE titleId = :number");
$myArr = array(':table'=> 'actor', ':number'=> $number);
$con->execute($myArr);
// change $myArr values and...
$con->execute($myArr)
8

what’s PDO?

perhaps you should start a new thread in the php forum :slight_smile: