MySQL 8.0

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