MySQLでON DUPLICATE KEY UPDATEをした際に元々入っていた値を使い回したい

Question

MySQLで下記のようなSQL文を実行した際に、思った結果とは違う結果になってしまいます。

INSERT INTO
    test(id, foo, bar, baz)
VALUES
    (1, 0, 0, 0)
ON DUPLICATE KEY UPDATE
    id = 1,
    foo = foo + 1,
    bar = foo + 1,
    baz = bar + 1;

なお、test テーブルには以下のようなデータが入っています。

|id(A_I)|foo|bar|baz|
|--------------|
|1|1|1|1|

実行結果がこうなってしまいます。

|id(A_I)|foo|bar|baz|
|--------------|
|1|2|3|4|

私が望んでた結果は以下のような感じなのですが、何か良い方法はありますか?

|id(A_I)|foo|bar|baz|
|--------------|
|1|2|2|2|

Answer 1

create table test(id int not null primary key auto_increment,foo int,bar int,baz int);
insert into test values(NULL,1,1,1);

だとして、

insert into test values(NULL, 0, 0, 0) on duplicate key update
id=1,foo = foo + 1,bar = foo + 1,baz = bar + 1;

してもduplicate keyはないので新しいデータが追加されるだけですよ

insert into test values(1, 0, 0, 0) on duplicate key update
baz = bar + 1,bar = foo + 1,foo = foo + 1;

のようidを明示してinsertして、逆からいれてあげるだけじゃないですか？

追記

あまり推奨されていませんが変数経由で処理することも検討ください

insert into test values(1, 0, 0, 0) on duplicate key update
foo = (@a:=foo) + 1,bar = (@b:=bar)*0 + @a + 1,baz = @b + 1;

Comments

[k725]

申し訳ないです。values(NULL, 0, 0, 0)ではなく、values(1, 0, 0, 0)でした。

[yambejp]

で、回答したとおりfoo→bar→bazをbaz→bar→fooのように
逆からいれてやる件はいかがでしょうか？

[yambejp]

変数経由の処理を追記しておきました

Answer 2

これでは事足り無そうな感じですが。

insert into test (id) values(1)
  ON DUPLICATE KEY UPDATE 
  foo = foo + 1,
  bar = foo,
  baz = bar;

こんな感じを拡張されてはいかがでしょうか？

insert into test select * from test s where s.id=1
ON DUPLICATE KEY UPDATE
 foo = test.foo + 1,
 bar = s.foo,
 baz = s.baz;

s.fooで更新前の値が入るのを利用する。

Comments

[k725]

実際には1を加算するのでは無く、CASE文でfooなどを比較する…というのをfoo, bar, bazの=以降でやっています。

```
(略)
foo = (CASE WHEN foo > bar THEN :hoge ELSE foo END), # ここは思った通り
bar = (CASE WHEN foo > bar THEN :fuga ELSE bar END), # fooが:hogeに変わった場合意図したとおりに動かない
baz = (CASE WHEN foo > bar THEN :piyo ELSE baz END); #〃
```

上記のような場合だと 条件自体が思っていたのと違ってしまうため困っています。

Answer 3

 foo = foo + 1,
    bar = foo + 1,
    baz = bar + 1;

ではなく

 foo = foo + 1,
 bar = bar + 1,
 baz = baz + 1;

したらなりませんか？

ｉｄがPKでしょうか？
(NULL, 0, 0, 0)、1ではなくNULLですか？

###追記

INSERT INTO
    test (id, foo, bar, baz)
VALUES
    (NULL, 0, 0, 0)
ON DUPLICATE KEY UPDATE
    id = 1,
    foo = foo + 1,
    bar = foo + 1,
    baz = bar + 1;

まずこれではvalueのidにNULLを指定しているのでai設定されているならどんどん(NULL, 0, 0, 0)でinsertされるだけでON DUPLICATE KEY UPDATEに入ってこないと思うのですが。。。。

Comments

[k725]

実際のSQLクエリではもう少し複雑になっており、CASE文を使用しているので厳しいです。

[hiim]

それではやらいたい事が少し見えてこないのですが、foo,bar,bazをインクリメントしたいという事ではないのですか？

[k725]

>ｉｄがPKでしょうか？
>(NULL, 0, 0, 0)、1ではなくNULLですか？
仰るとおりIDがプライマリキーになっています。(A_Iも有効にしているのでNULLで間違いないです)

[k725]

>それではやらいたい事が少し見えてこないのですが、foo,bar,bazをインクリメントしたいという事ではないのですか？
インクリメントしたいわけではありません。

[k725]

失礼しました。確かにNULLではなく1ですね。 修正しました。