CODEWITHSUNDEEP
mysqlsql-update
Dan
Dan

Reputation: 574

Unknown column in subquery for update

I have an update statement which I would like to do in a single statement, but I am finding it a bit tricky.

A table records the changes of a target over time.

For example, from the 1st to the 31st of January the target can be 100, then from the 1st to the 28th of February 110, then from the 1st to the 31st of March 120 and so on.

The user only enters the amount and date_from. The date_to of every record is recalculated after every time a record is deleted, updated or a new one is inserted. The value it picks up is the day before the day_from of the record with the minimum day_from bigger than the current record's day_from. Since I can't think of a way to make it a bit more clear, here are a few examples:

From            To            Amount
1st Jan         31st Jan      100
1st Feb         28th Feb      110
1st Mar         31st Mar      120
...
...

If I insert a record with date_from 15th of Jan and value = 105, I would like the record for the 1st of January to have date_to updated to 14th of January, and the inserted one have date_to updated to 31st of January

Deleting the record for the 1st of February would have the date_to of the new record of the newly inserted record changed from the 31st of January, to the 28th of March.

I hope it explains what I am trying to do clearly enough.

Now, here is my SQL, which doesn't work

The following returns:

Error Code: 1093. You can't specify target table 'ptcth1' for update in FROM clause

update personal_trainer_client_target_history ptcth1 set date_to = 
(
    select min(date_from) from personal_trainer_client_target_history ptcth2 
    where ptcth2.date_from > ptcth1.date_from
)

When I try to correct it like this, I get => Error Code: 1054. Unknown column 'ptcth1.date_from' in 'where clause'

update personal_trainer_client_target_history ptcth1 set date_to = 
(
  select date_from from 
  (
    select min(date_from) from personal_trainer_client_target_history ptcth2 
    where ptcth2.date_from > ptcth1.date_from
  ) 
  as temp_table
)

Any idea?

Upvotes: 0

Views: 191

Answers (1)

Strawberry
Strawberry

Reputation: 33945

    DROP TABLE IF EXISTS my_table;

    CREATE TABLE my_table (from_date DATE NOT NULL PRIMARY KEY,Amount INT NOT NULL);

    INSERT INTO my_table VALUES 
    ('2013-01-01',100),
    ('2013-02-01',110),
    ('2013-03-01',120);

    SELECT x.from_date
         , MIN(y.from_date) - INTERVAL 1 DAY to_date 
         , x.amount
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON y.from_date > x.from_date 
     GROUP 
        BY x.from_date;
    +------------+------------+--------+
    | from_date  | to_date    | amount |
    +------------+------------+--------+
    | 2013-01-01 | 2013-01-31 |    100 |
    | 2013-02-01 | 2013-02-28 |    110 |    
    | 2013-03-01 | NULL       |    120 |
    +------------+------------+--------+

    INSERT INTO my_table VALUES ('2013-01-15',105);

    SELECT x.from_date
         , MIN(y.from_date) - INTERVAL 1 DAY to_date 
         , x.amount
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON y.from_date > x.from_date 
     GROUP 
        BY x.from_date;
    +------------+------------+--------+
    | from_date  | to_date    | amount |
    +------------+------------+--------+
    | 2013-01-01 | 2013-01-14 |    100 |
    | 2013-01-15 | 2013-01-31 |    105 |
    | 2013-02-01 | 2013-02-28 |    110 |
    | 2013-03-01 | NULL       |    120 |
    +------------+------------+--------+

Upvotes: 1

Related Questions