Possible datatype error when using INSERT INTO, ON DUPLICATE KEY and IF() in a prepared statement-CodePudding

I am trying to send a kind of notification to not update a field during an INSERT INTO, ON DUPLICATE KEY UPDATE query, which I outlined in this post. (Possible to do a MYSQL UPDATE query but not actually update a column?)

I am following this model which was kindly provided as a reply to that post. (https://stackoverflow.com/a/74418601)

UPDATE homes SET ..., name = if(?='DO NOT UPDATE',name,?) WHERE id=?
$stmt2->execute([..., $row['name'], $row['name'], $row['id']]);

The problem is that it appears (not certain) that there are type errors in my own go at following this model.

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?, ?),(?, ?, ?) AS foo_baz
ON DUPLICATE KEY UPDATE 
name = if(foo_baz.name='DO NOT UPDATE', user.name, foo_baz.name),
age = if(foo_baz.age='DO NOT UPDATE', user.age, foo_baz.age)";

$data = [1, 'Oregon', 55, 2, 'Washington', 'DO NOT UPDATE'];

$sql = $pdo->prepare($stmt);
$sql->execute($data);

The datatypes of the id column is signed int, the state column is varchar, and age is signed int.

When I run this it says

"Invalid datetime format: 1292 Truncated incorrect DOUBLE value: " for the age column

This is probably because 'DO NOT UPDATE' is being placed into an int column, age.

The INSERT INTO, ON DUPLICATE KEY UPDATE is ONLY being used as a way update. All of the data being inserted already has a row with the primary key already existing, so there are no actual inserts. If it were an actual insert, the error would make sense, since it would be trying to insert the wrong datatype into a field. But since it is only updating, the data is not actually inserted (because it is 'DO NOT UPDATE'). I read that mysql tries to insert and then update internally when doing a INSERT INTO, ON DUPLICATE KEY UPDATE query.

I did try casting the variable but the error repeated itself because the error is probably given in the INSERT part of the query, before the ON DUPLICATE KEY UPDATE part of the query. (This probably would not even work, since 'DO NOT UPDATE' is not an int, and might always give a false negative or positive.)

age = if(foo_baz.age=CAST('DO NOT UPDATE' AS SIGNED), user.age, foo_baz.age)

If I am interpreting the error correctly (not certain), what I want to accomplish is a technique to put something (doesn't have to be DO NOT UPDATE) into $data for values that I do not want it to update, and have a way to indicate that for ANY datatype column, so any suggestion on how to fix what I have done or accomplish this in another way would be helpful as long as I can do it in the context of an INSERT INTO, ON DUPLICATE KEY UPDATE query.

CodePudding user response：

If you want to accomplish the same thing (using the sql if() to determine what value(s) to update) as in the previous related post, please change the following

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?, ?),(?, ?, ?) AS foo_baz
ON DUPLICATE KEY UPDATE 
......

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?,  if(?='DO NOT UPDATE',age,?)),(?, ?,  if(?='DO NOT UPDATE',age,?)) AS foo_baz
ON DUPLICATE KEY UPDATE 
......

and make sure you pass the value for age (which may be DO NOT UPDATE) to execute twice for each record

So change from

$data = [1, 'Oregon', 55, 2, 'Washington', 'DO NOT UPDATE'];

$data = [1, 'Oregon', 55, 55, 2, 'Washington', 'DO NOT UPDATE', 'DO NOT UPDATE'];