Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

(OT) MySQL: delete from multiple tables

by Anonymous Monk
on Mar 14, 2005 at 15:21 UTC ( #439302=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
I am having some trouble deleting values from multiple table in mysql. the code I have written works fine, but it seems it keeps failing on the actual statement issued to mysql.
I have several tables, each with a field called data_id. The rest of the fields can vary from table to table. What I would like to do is simply delete all rows from the specified tables where data_id='111'. (data_id isn't unique, so many rows can have the data_id 111 in each table.
This is my sql whats wrong with this!?

DELETE FROM table1, table2, table3 WHERE data_id="111";

thanks in advance, Steve

20050315 Edit by castaway: Changed title from 'MySQL: delete from multiple tables'

  • Comment on (OT) MySQL: delete from multiple tables

Replies are listed 'Best First'.
[OT] Re: MySQL: delete from multiple tables
by trammell (Priest) on Mar 14, 2005 at 16:37 UTC
    Questions specific to MySQL are probably better asked on one of the MySQL mailing lists. I've had good luck on the "General Discussion" list.
Re: (OT) MySQL: delete from multiple tables
by jhourcle (Prior) on Mar 14, 2005 at 15:28 UTC

    The correct delimiter in SQL is single quotes.

    DELETE FROM table1 WHERE data_id='111'; DELETE FROM table2 WHERE data_id='111'; DELETE FROM table3 WHERE data_id='111';

    mySQL will support deletes from multiple tables, if you're using mySQL 4.0 or later, but I don't suggest it, as it can lock you into using mySQL, and it would mean that should you want to change out your backend, you'll have to go through and remove every mySQL-ism in your code. I find it better to be database-ambiguous unless it's something that you can't do efficiently without it.

    Update: I stand corrected -- recent versions of mySQL supports double quotes as a delimiter. I would still advise against it, so you don't have problems should you later move to Oracle, or some other less forgiving database.

      Have you tried using double quotes in mySQL? I'm pretty sure it works. And I don't think you mean 'delimiter', either. You probably mean something to do with quoting. Whatever, he should be using placeholders.

      (A problem I see is that he has a ';' on the end, and if you pass that in via DBI or whatever, it'll screw things up)

      OK, maybe delimiter might be the right word. I've been wrong before :)
Re: (OT) MySQL: delete from multiple tables
by bpphillips (Friar) on Mar 14, 2005 at 18:06 UTC
    as noted above, this isn't the best place for SQL questions but here's how you would do that in recent (>= 4.0) versions of MySQL:
    DELETE FROM table1, table2, table3 USING table1 INNER JOIN table2 USING(data_id) INNER JOIN table3 USING(data_id) WHERE table1.data_id = 111
    Starting in MySQL 4.1 you can use table aliases (must be consistent in both the FROM and USING clauses
      Thanks...this works
Re: (OT) MySQL: delete from multiple tables
by perl_lover (Chaplain) on Mar 14, 2005 at 16:20 UTC

    For deleting multiple tables you need to use like this.

    DELETE table1.*, table2.*,table3.* FROM table1, table2, table3 WHERE table1.data_id="111";

Re: (OT) (OT) MySQL: delete from multiple tables
by Anonymous Monk on Mar 14, 2005 at 15:25 UTC
    Oh, this is the error i get in mysql:

    064: 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 '* FROM hotfix, softwareinstalled WHERE data_set="temporary"' at

    cheers, Steve

      That's a different problem. Well, there's still the problem with the use of double quotes instead of single quotes, but I'm guessing you also tried specify the fields as *. Delete does not take a field list, as it removes all fields. If you want to use the multi-table delete (which I still don't recommend), you should use:

      DELETE FROM hotfix, softwareinstalled WHERE data_set='temporary';

      and not

      DELETE * FROM hotfix, softwareinstalled WHERE data_set="temporary";

      Update: double quotes aren't a problem in mySQL, as per Jasper

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://439302]
Approved by moot
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2017-04-24 05:11 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (433 votes). Check out past polls.