By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
create table foo (id serial, bar text, updated int not null default 0);
insert into foo (bar) values ('abc')
-- deprecated syntax, not working
insert into foo (id,bar) values (1,'def')
on duplicate key update
bar=values(bar),
updated=case when foo.bar<>values(bar) then 1 else 0 end
select * From foo;
| id | bar | updated |
|---|---|---|
| 1 | def | 0 |
-- deprecated syntax, working
insert into foo (id,bar) values (1,'ghi')
on duplicate key update
updated=case when foo.bar<>values(bar) then 1 else 0 end,
bar=values(bar)
select * from foo;
| id | bar | updated |
|---|---|---|
| 1 | ghi | 1 |
-- new syntax, not working
insert into foo (id,bar) values (1,'jlk') as newvalues
on duplicate key update
bar=newvalues.bar,
updated=case when foo.bar<>newvalues.bar then 1 else 0 end
select * from foo
| id | bar | updated |
|---|---|---|
| 1 | jlk | 0 |
-- new syntax, working
insert into foo (id,bar) values (1,'mno') as newvalues
on duplicate key update
updated=case when foo.bar<>newvalues.bar then 1 else 0 end,
bar=newvalues.bar
select * from foo
| id | bar | updated |
|---|---|---|
| 1 | mno | 1 |