CODEWITHSUNDEEP
mysqlsqlselectjoininsert
Koralek M.
Koralek M.

Reputation: 3341

MySQL - Insert changes from one table to another

I have a simplified table:

id     date     color
----------------------
1   2012-01-01  black
2   2012-01-02  red
3   2012-01-03  red
4   2012-01-04  red
5   2012-01-05  green

And now I need to insert a color change from this table into new one:

   date     before   now
----------------------------
2012-01-02  black    red
2012-01-04  red      green

Can someone tell me how, please?

Upvotes: 0

Views: 404

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

Compare second row to previous row:

SELECT cur.date, prev.color "before", cur.color now
FROM tbl cur
LEFT JOIN tbl prev ON cur.id = prev.id + 1
WHERE cur.id > 1 -- start detecting changes from second row
     AND prev.color <> cur.color

Live test: http://sqlfiddle.com/#!2/0c146/1

EDIT

Works even in non-contiguous data. Query could be more succint if there's CTE functionality in MySQL

create table tbl
(
  id int, 
  date date,
  color text
);


insert into tbl(id,date,color)
select 1,'2012-1-1','black' union
select 2,'2012-1-3','red' union
select 3,'2012-1-7','red' union
select 4,'2012-1-15','red' union
select 5,'2012-1-21','green' ;


set @rx = 0;
set @ry = 0;


select cur.date, prev.color as "before", cur.color as "now"

from (select *, @ry := @ry + 1 as series from tbl order by date) as cur 
left join (select *, @rx := @rx + 1 as series from tbl order by date) as prev
on cur.series = prev.series + 1

where cur.series > 1
and prev.color <> cur.color

Live test: http://sqlfiddle.com/#!2/b9443/2

Upvotes: 3

Woyzeck
Woyzeck

Reputation: 115

If I understand you right, stored procedures are what you need.

Stored Procedures

Upvotes: 0

Alexander Pavlov
Alexander Pavlov

Reputation: 32286

First, rename the color column -> before, then add the now column (default null if possible), then update the now column as needed.

Upvotes: 0

Related Questions