1

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;
Yury
  • 722
  • 7
  • 14

1 Answers1

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yes, I totally forgot about the order - `ORDER BY id, c1` worked for me! Good point about not needing the dummy columns. Thanks! – Yury Oct 04 '17 at 04:18
  • Just curious why `ORDER BY id` alone doesn't do it for me - any ideas? AFAIK, there are no nulls in either column (already shut down, but will check in the morning). – Yury Oct 04 '17 at 04:41
  • Since there are multiple rows with each id value, there are ties, and the order of rows with the same id is unpredictable. Sorry I forgot about that in my answer. – Bill Karwin Oct 04 '17 at 05:59
  • Hmm, but id is a (unique) primary key, both in your table and mine. Here's the bigger mystery: I have 2 versions of this table, dev and production. To my knowledge, they are identical up to a certain point, after which dev stops and production has more of the same kind of data. The first time I ran the query on dev, it updated 0 rows. So then I added the `, c1` and it seemed to work correctly. Now I run the query with `ORDER BY id, c1` on production - nothing again! I run the same exact query again, and it works?!? So it wasn't the `, c1` at all, but the mere fact of rerunning the query?? – Yury Oct 04 '17 at 10:30
  • I can't guess without seeing your table definition. Anyway, this is a problem that's always tricky, because in SQL, rows aren't *supposed* to have any implicit order. – Bill Karwin Oct 04 '17 at 15:24