Trying to do the following: if current row's column c1 is equal to previous row's c1, set column c3 to previous row's column c2; otherwise, set c3 to NULL (or just don't set it to anything). Can someone tell me why the following query results in c3 being null for every row?
The dummy columns are just there to be able to set the variables @c1 and @c2, which are supposed to store this row's c1 and c2 values to be used for the next row. BTW, am I wrong in assuming that c3 will be updated first, i.e. it will get the previous value of @c2 (before @c2 gets assigned to the current c2)?
UPDATE t SET c3 = IF (c1 = @c1, @c2, NULL), dummy1 = @c1:=c1, dummy2 = @c2:=c2;
I tried this and it seems to work fine. But you need an ORDER BY if you need to evaluate the rows in some specific order.
create table t (c1 int, c2 int, c3 int, id int auto_increment primary key);
insert into t (c1, c2, c3) values
(1, 2, 3),
(1, 4, 5),
(2, 6, 7);
select * from t;
+------+------+------+----+
| c1 | c2 | c3 | id |
+------+------+------+----+
| 1 | 2 | 3 | 1 |
| 1 | 4 | 5 | 2 |
| 2 | 6 | 7 | 3 |
+------+------+------+----+
update t set c3=if(c1=@c1,@c2,NULL), c1 = @c1:=c1, c2 = @c2:=c2 order by id;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
select * from t;
+------+------+------+----+
| c1 | c2 | c3 | id |
+------+------+------+----+
| 1 | 2 | NULL | 1 |
| 1 | 4 | 2 | 2 |
| 2 | 6 | NULL | 3 |
+------+------+------+----+
Note that I didn't need dummy columns. Just set c1=c1 and c2=c2, because those are no-ops.