Mysql on duplicate key condition (IF) setting of one field

Question

I am using on duplicate key to update data in a mySQL DB v 8.0.35 (AWS RDS instance)

I want to update a selection of fields with the new value. Plus set a further field (up[dated) to 1 only if a specific field (templateURL) has changed. This should be left at the old value (0) if only other fields have changed.

I believe I ought to be able to use IF or CASE to achieve this based on previous posts to similar questions. However, it looks like this used to work with the old values(field) syntax, bit does not work with the new alias syntax.

I am getting an "You have an error in your SQL" error when running this SQL:

insert into data_manager.rars_CoachTypes c
  
(coach_type,template_height,template_width,seat_height,seat_width,toc,templateURL,serviceId,latest_depart_date,our_template_width,our_template_height,features,imported) values ? as INSERTDATA
  on duplicate key update
    template_height=INSERTDATA.template_height,
    template_width=INSERTDATA.template_width,
    seat_height=INSERTDATA.seat_height,
    seat_width=INSERTDATA.seat_width,
    templateURL=INSERTDATA.templateURL,
    latest_depart_date=INSERTDATA.latest_depart_date,
    updated = IF (c.templateURL<>INSERTDATA.templateURL,1,0)

I've also tried it with CASE rather than IF, but the result is the same.

    updated = CASE WHEN c.templateURL<>INSERTDATA.templateURL THEN 1 ELSE 0 END

I added the alias "c" because I was previously getting another error saying the field templateURL was ambiguous... Not sure why it should think that?

Answer 1

For mysql your syntax is very off

see manual

it mus be

insert into data_manager.rars_CoachTypes 
  
(coach_type,template_height,template_width,seat_height,seat_width,toc,templateURL,serviceId,latest_depart_date,our_template_width,our_template_height,features,imported) values (1,1,1,1,1,1,1,1,1,1) 
  on duplicate key update
    template_height=VALUES(template_height),
    template_width=VALUES(template_width),
    seat_height=VALUES(seat_height),
    seat_width=VALUES(seat_width),
    templateURL=VALUES(templateURL),
    latest_depart_date=VALUES(latest_depart_date),
    updated = IF (templateURL<>VALUES(templateURL),1,0);

or as Barmar correctly said, you can use use OLD and NEW to differentiate both values

insert into data_manager.rars_CoachTypes 
  
(coach_type,template_height,template_width,seat_height,seat_width,toc,templateURL,serviceId,latest_depart_date,our_template_width,our_template_height,features,imported) values (1,1,1,1,1,1,1,1,1,1) as NEW
  on duplicate key update
    template_height=NEW.template_height,
    template_width=NEW.template_width,
    seat_height=NEW.seat_height,
    seat_width=NEW.seat_width,
    templateURL=NEW.templateURL,
    latest_depart_date=NEW.latest_depart_date,
    updated = IF (templateURL<> NEW.templateURL,1,0)

Answer 2

Order matters; you must set updated before setting templateURL, or your expression will compare the new value to the value to be set (resulting in updated always being 0). This was true using the VALUES() function and remains true using the VALUES alias.

fiddle

In general, when you use a column value in an expression in an update, it has the value as of the time it gets to that expression. Multitable updates work a little differently, though not in a way that makes any sense.

Your if syntax was incorrect; you were missing 2 parameters:

IF (c.templateURL<>INSERTDATA.templateURL,1,0)

with those, either IF or your CASE will work (with updated= moved before templateURL=). When you get a "You have an error in your SQL" error, look closely at where it said the error occurs (and what immediately precedes that ). That could have clued you in to the bad IF syntax.

About aliases, on the left side of the column update =, an identifier can only refer to one of the tables being updated, so templateURL= is unambiguous, but on the right, templateURL could refer to either rars_CoachTypes or INSERTDATA, so needs to be qualfied. You should explicitly say data_manager.rars_CoachTypes.templateURL.

Answer 3

Sorry for the confusion over the IF syntax. My real SQL is somewhat more complex and I had removed some to make it simpler for this post and accidentally removed the TRUE/FALSE parameters as well... That was not the problem.

It turns out that the ALIAS is the problem. If I add an alias I get the error. Remove it and the error goes away. Not sure why an alias is not permitted here...?

My SQL now looks like this and works:

insert into data_manager.rars_CoachTypes
  (coach_type,template_height,template_width,seat_height,seat_width,toc,templateURL,serviceId,latest_depart_date,our_template_width,our_template_height,features,imported) values ? as NEW
  on duplicate key update
    updated = IF(OLD.templateURL <> NEW.templateURL,1,0),
    template_height=NEW.template_height,
    template_width=NEW.template_width,
    seat_height=NEW.seat_height,
    seat_width=NEW.seat_width,
    templateURL=NEW.templateURL,
    latest_depart_date=NEW.latest_depart_date

Thanks to @ysth for pointing out that the order is important for ensuring "updated" get sets correctly and for suggesting I reference the table directly instead of using an alias which ultimately was coirrect way to remove the error.