Question 345813 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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)

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?

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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. Your alias works fine, but you could have not used an alias and explicitly said data_manager.rars_CoachTypes.templateURL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search