Page 1 of 2

[MySQL] Using JOIN in DELETE query

Posted: Thu Feb 21, 2008 3:37 pm
by nutkenz
My query:

Code: Select all

DELETE FROM criterion_stats
 JOIN criterions ON criterion_stats.criterion_id = criterions.id
 JOIN adgroups ON adgroup_id = adgroups.id
 JOIN campaigns ON campaign_id = campaigns.id WHERE keyword IN ('k1','kw2');
 
The error:

#1064 - 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 'JOIN criterions ON criterion_stats.criterion_id = criterions.id
JOIN adgroups ' at line 2

Though according to the MySQL Manual, I'm allowed to use JOIN statements in a delete query:
MySQL supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements

Code: Select all

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

Re: [MySQL] Using JOIN in DELETE query

Posted: Thu Feb 21, 2008 10:25 pm
by califdon
Just what are you trying to delete? Rows in all 3 tables? And in which table is the column keyword?

Re: [MySQL] Using JOIN in DELETE query

Posted: Fri Feb 22, 2008 2:47 am
by nutkenz
califdon wrote:Just what are you trying to delete? Rows in all 3 tables? And in which table is the column keyword?
No, only from the criterion_stats table. The keyword is in the criterions table but sometimes I need to select on a column in the adgroups or campaigns table which is why they're mentioned.

Re: [MySQL] Using JOIN in DELETE query

Posted: Fri Feb 22, 2008 12:52 pm
by califdon
nutkenz wrote:

Code: Select all

DELETE FROM criterion_stats
 JOIN criterions ON criterion_stats.criterion_id = criterions.id
 JOIN adgroups ON adgroup_id = adgroups.id
 JOIN campaigns ON campaign_id = campaigns.id WHERE keyword IN ('k1','kw2');
califdon wrote:Just what are you trying to delete? Rows in all 3 tables? And in which table is the column keyword?
No, only from the criterion_stats table. The keyword is in the criterions table but sometimes I need to select on a column in the adgroups or campaigns table which is why they're mentioned.
I'm struggling to understand why you're involving so many tables, although I realize that if I really understood your whole situation, it might be perfectly sensible. I would be quite cautious about any complex DELETE query, though; just one slight error could wipe out a lot of your data without the possibility of recovery.

So let's see if I have this correct: in your example, you want to delete any rows in criterion_stats if a matching row in criterions contains a value of either 'k1' or 'kw2' in the keyword column?

Your syntax, with the extra joins to adgroups and campaigns, will have the effect of not permitting you to delete anything if, for example, there happened to be no matching row in either adgroups or campaigns, even if there is a matching row in criterions and it has the indicated keyword.

Honestly, I have never used a multiple table DELETE query, but I note in the manual that there is an optional USING clause, so I'm wondering if the following would eliminate the error?

Code: Select all

DELETE FROM criterion_stats USING criterion_stats, criterions, adgroups, campaigns
  WHERE criterion_stats.criterion_id = criterions.id
  AND adgroups.adgroup_id = adgroups.id
  AND campaigns.campaign_id = campaigns.id
  AND criterions.keyword IN ('k1','kw2');

Re: [MySQL] Using JOIN in DELETE query

Posted: Fri Feb 22, 2008 4:34 pm
by nutkenz
I'm struggling to understand why you're involving so many tables, although I realize that if I really understood your whole situation, it might be perfectly sensible. I would be quite cautious about any complex DELETE query, though; just one slight error could wipe out a lot of your data without the possibility of recovery.
I do need those tables or the column in the WERE part of the query will not be known
So let's see if I have this correct: in your example, you want to delete any rows in criterion_stats if a matching row in criterions contains a value of either 'k1' or 'kw2' in the keyword column?
Yes, in this case the keyword is contained in the criterions table, but I might as well have used WHERE advertiser_id = 'something' in which case I would have needed the campaigns table. They are all included all of the time because the query is composed dynamically with PHP.
Your syntax, with the extra joins to adgroups and campaigns, will have the effect of not permitting you to delete anything if, for example, there happened to be no matching row in either adgroups or campaigns, even if there is a matching row in criterions and it has the indicated keyword.
There should always be a matching row in the tables which are joined because the system operates that way.
Honestly, I have never used a multiple table DELETE query, but I note in the manual that there is an optional USING clause, so I'm wondering if the following would eliminate the error?
Isn't using supposed to be used like this?

Code: Select all

JOIN table USING (some_id)
Where both tables would have the some_id column and a row with matching values. Either way, your query results in the following error: #1054 - Unknown column 'adgroups.adgroup_id' in 'where clause'

Re: [MySQL] Using JOIN in DELETE query

Posted: Fri Feb 22, 2008 7:25 pm
by califdon
nutkenz wrote:Yes, in this case the keyword is contained in the criterions table, but I might as well have used WHERE advertiser_id = 'something' in which case I would have needed the campaigns table. They are all included all of the time because the query is composed dynamically with PHP.
In which case, I recommend that you dynamically create the query with the correct tables involved. Not only for improved performance, but again, to make the logic of the query correct, instead of bringing in tables that introduce conditions that are not part of your business logic.
There should always be a matching row in the tables which are joined because the system operates that way.
"should always be" -- aye, there's the rub, as Hamlet said. The point is, you'd never know if some record had somehow gotten deleted, the query just wouldn't work correctly. That's not a good way to design a system.
Isn't using supposed to be used like this?

Code: Select all

JOIN table USING (some_id)
As I said, I've never used this syntax, but my reading of the manual leads me to understand that it's definitely talking about tables, not columns.
Where both tables would have the some_id column and a row with matching values. Either way, your query results in the following error: #1054 - Unknown column 'adgroups.adgroup_id' in 'where clause'
Well, I don't know your table schema, do I? I was guessing and no doubt guessed wrong.

Re: [MySQL] Using JOIN in DELETE query

Posted: Sat Feb 23, 2008 1:12 am
by Benjamin
These are untested..

Use this to only delete records from the criterion_stats table

Code: Select all

 
DELETE
  cs
FROM
  criterion_stats cs
WHERE
  cs.keyword IN ('k1', 'kw2')
  AND cs.criterion_id = criterions.id
  AND cs.adgroup_id = adgroups.id
  AND cs.campaigns = campaigns.id
 

Use this to delete records from all four tables.

Code: Select all

 
DELETE
  cs, c, a, c2
FROM
  criterion_stats cs,
  criterions c,
  adgroups a,
  campaigns c2
WHERE
  cs.keyword IN ('k1', 'kw2')
  AND cs.criterion_id = c.id
  AND cs.adgroup_id = a.id
  AND cs.campaigns = c2.id
 

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 3:42 am
by nutkenz
astions wrote:These are untested..

Use this to only delete records from the criterion_stats table

Code: Select all

 
DELETE
  cs
FROM
  criterion_stats cs
WHERE
  cs.keyword IN ('k1', 'kw2')
  AND cs.criterion_id = criterions.id
  AND cs.adgroup_id = adgroups.id
  AND cs.campaigns = campaigns.id
 

Use this to delete records from all four tables.

Code: Select all

 
DELETE
  cs, c, a, c2
FROM
  criterion_stats cs,
  criterions c,
  adgroups a,
  campaigns c2
WHERE
  cs.keyword IN ('k1', 'kw2')
  AND cs.criterion_id = c.id
  AND cs.adgroup_id = a.id
  AND cs.campaigns = c2.id
 
The first query can't work because the keywoord isn't known in the criterion_stats table. I can't try the second one because I only want to delete from the first table.

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 9:16 am
by Benjamin
The first query shouldn't be hard to modify then ;)

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 12:12 pm
by nutkenz
astions wrote:The first query shouldn't be hard to modify then ;)
Yes, by adding the joins?

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 1:13 pm
by Benjamin
I think I'll let you ponder that for a while.

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 1:29 pm
by nutkenz
astions wrote:I think I'll let you ponder that for a while.
Nope, it's not coming to me. I don't see how I can delete a row in the criterion_stats table based on a column which is unknown in that table (keyword) without doing a JOIN first.

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 1:35 pm
by Benjamin
What table is it in?

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 1:43 pm
by nutkenz
astions wrote:What table is it in?
In the criterions table. Sometimes I need to select based on columns in the campaigns table too.

Re: [MySQL] Using JOIN in DELETE query

Posted: Sun Feb 24, 2008 1:49 pm
by Benjamin
Ok, so you can change the table by changing the alias. You don't need to explicitly use the JOIN keyword to join tables. In fact, not using it allows mysql to optimize the order in which the tables are joined in.

Code: Select all

 
DELETE
  cs
FROM
  criterion_stats cs,
  criterions c
WHERE
  c.keyword IN ('k1', 'kw2')
  AND cs.criterion_id = c.id
  AND cs.adgroup_id = adgroups.id
  AND cs.campaigns = campaigns.id
 
I'm not sure why your joining adgroups and campaigns unless your verifying that a record exists in those tables are part of the delete criteria.