1
INSERT INTO 'rozliczenia' ('userid', 'data', 'stawka') VALUES ('1', '2015-11-01', NULL) ON DUPLICATE KEY UPDATE stawka = COALESCE(VALUES('stawka'), 'stawka');

It should check if value is NULL. If it is then don't update premia column(I don't have any null values in table), but it updates it to 0. Why? Everything worked fine yesterday.

Before update:

Premia 200

After

Premia 0, though it should be 200

Indexes in the table:

Primary - Id

Unique - data

Unique - userid

IT IS NOT ABOUT QUOTES, WHEN CHANGE THEM IT BEHAVES THE SAME WAY

1 Answers1

1

You have single quotes around table and column names, so your query shouldn't work.

INSERT INTO rozliczenia(userid, data, stawka)
    VALUES ('1', '2015-11-01', NULL)
    ON DUPLICATE KEY UPDATE stawka = COALESCE(VALUES(stawka), stawka);

The reason why coalesce() returns 0 is because you are passing in a string and then using it in a numeric context. A string that doesn't start with a digit ends up being 0 in such a context.

Repeat until you understand it thoroughly: "I will only use single quotes for string and date constants."

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • can you please remove ON DUPLICATE ..... to see you get an error – Bernd Buffen Oct 11 '15 at 20:14
  • Now it gives me error Column stawka cannot be null –  Oct 11 '15 at 20:16
  • Why is that happening –  Oct 11 '15 at 20:22
  • When I remove on duplicate key or gives me Duplicate entry '1-2015-11-01' for key userdata –  Oct 11 '15 at 20:23
  • I removed single quotes and its still not working –  Oct 11 '15 at 20:27
  • if have tested it on my MariaDB 10.0.21 Server with this structure: CREATE TABLE `rozliczenia` ( `userid` int(11) unsigned NOT NULL AUTO_INCREMENT, `data` date DEFAULT NULL, `stawka` varchar(32) DEFAULT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; AND IT WORKS FINE. NO PROBLEMS. NEVER NULL IN IT – Bernd Buffen Oct 11 '15 at 20:39