Delete with Join

[davidannis]

I have two tables, one (projection) with a description of each item in a projected financial statement and the second (projection_detail) with detail for each row. The detail file can have 1 (annual), 4 (quarterly), or 12 (monthly) rows for each row in the projection file. Table structures are projection:

+----------------------+-----------------------+------+-----+---------+----------------+
| Field                | Type                  | Null | Key | Default | Extra          |
+----------------------+-----------------------+------+-----+---------+----------------+
| projection_id        | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| company_valuation_id | mediumint( unsigned | NO   | MUL | NULL    |                |
| year                 | smallint(5) unsigned  | NO   |     | NULL    |                |
| category_code        | char(4)               | NO   |     | NULL    |                |
| description          | varchar(60)           | NO   |     | NULL    |                |
| amount               | decimal(12,2)         | NO   |     | NULL    |                |
+----------------------+-----------------------+------+-----+---------+----------------+

and projection_detail

mysql> describe projection_detail;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| projection_id | int(10) unsigned     | NO   | PRI | NULL    |       |
| period        | smallint(5) unsigned | NO   | PRI | NULL    |       |
| amount        | decimal(12,2)        | NO   |     | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+

I can select all the data for a year using something like this:

SELECT *
FROM projection
JOIN projection_detail ON projection.projection_id = projection_detail.projection_id
WHERE company_valuation_id = '1'
AND year = '1'

but I want to DELETE those rows from both tables and this gives me an error.

DELETE FROM projection JOIN projection_detail ON projection.projection_id=projection_detail.projection_id WHERE company_valuation_id='75' and year='1' 

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 projection_detail ON projection.projection_id=projection_detail.projection_' at line 1

How do I delete a year's data from both tables without having to loop in php?

 

Thanks,

David

 

Edit: The amount column in projection is left over from when all projections were annual and will be deleted.

I'm open to other suggestions on a better way to structure the tables.

[Psycho]

You can't delete from multiple tables in one query. You need to delete from the child table (projection_detail) first, then the parent table (projection).

 

Query to delete from projection_detail table

DELETE FROM projection_detail
WHERE projection_id
   IN (
       SELECT projection_id
       FROM projection
       WHERE company_valuation_id = '1'
         AND year = '1'
      )

This may need an alias on the sub-query, but I don't think so

 

Query to delete from projection table

DELETE FROM projection
WHERE company_valuation_id = '1'
  AND year = '1'

[Jacques1]

Of course you can delete from multiple tables with a single query. You just need to get the syntax right, and the manual will be helpful for that.

[davidannis]

I have tried to delete from multiple tables using a variety of queries and I'm getting closer.

DELETE projection, projection_detail FROM projection INNER JOIN projection_detail 
ON projection.projection_id=projection_detail.projection_id WHERE company_valuation_id='1' and year='1'

almost works. However, unless my testing is wrong, it leaves rows in the projection table that have no detail in the projection detail table.

[Psycho]

 unless my testing is wrong, it leaves rows in the projection table that have no detail in the projection detail table.

 

That's because an INNER JOIN only returns records where there are records to be JOINed from both the LEFT and RIGHT tables. If a record in either table cannot be JOINed to a record in the other table it will not be included in the results (or in this case included in the Delete operation). Try using a LEFT JOIN

 

DELETE projection, projection_detail
FROM projection
LEFT JOIN projection_detail
  ON projection.projection_id=projection_detail.projection_id
WHERE company_valuation_id='1'
 AND year='1'