CODEWITHSUNDEEP
mysql
jhon dano
jhon dano

Reputation: 658

Update newest row with data from oldest row in mysql

How can i update certain fields in my table with data from older entries?

Example Table:

    id | timestamp    | columne1| columne2
    ---------------------------------------
    1  | 2015-07-20...| 078888  | *****
    2  | 2015-07-19...| 155896  | value 2.2
    3  | 2015-07-07...| 278831  | value 2.3
    4  | 2015-07-01...| 078888  | value 2.4

I am trying to update columne2 from row 1 with the value in same columne2 row 4.

Upvotes: 0

Views: 59

Answers (1)

amdixon
amdixon

Reputation: 3833

plan

  • select the minimum entries (maxs) grouped by columne1,
  • select the maximum entries (mins) grouped by columne1,
  • join calls to maxs to mins
  • set calls.columne2 value from mins data

query

update
calls c
inner join
(
  select c1.*, q1.min_ts, q1.max_ts
  from calls c1
  inner join
  ( select columne1, min(`timestamp`) min_ts, max(`timestamp`) as max_ts from calls group by columne1 ) q1
  on c1.columne1 = q1.columne1
  where c1.`timestamp` = q1.max_ts
) maxs
on c.id = maxs.id
inner join
(
  select c1.*, q1.min_ts, q1.max_ts
  from calls c1
  inner join
  ( select columne1, min(`timestamp`) min_ts, max(`timestamp`) as max_ts from calls group by columne1 ) q1
  on c1.columne1 = q1.columne1
  where c1.`timestamp` = q1.min_ts
) mins
on maxs.columne1 = mins.columne1
set c.columne2 = mins.columne2
;

output

+----+------------------+----------+-----------+
| id |    timestamp     | columne1 | columne2  |
+----+------------------+----------+-----------+
|  1 | 2015-07-20 12:00 |   078888 | value 2.4 |
|  2 | 2015-07-19 12:00 |   155896 | value 2.2 |
|  3 | 2015-07-07 12:00 |   278831 | value 2.3 |
|  4 | 2015-07-01 12:00 |   078888 | value 2.4 |
+----+------------------+----------+-----------+

sqlfiddle

Upvotes: 2

Related Questions