I would like to only update a column in a table if, the input value is not null or a empty string. If it is empty or null I would want to use the already existing columns value or not changing it at all. So

I have made this Query, but it seems a bit off as I am getting parse errors when executing the query.

SET
    @INPUT_NICK = ?,
SET
    @INPUT_EMAIL = ?,
SET
    @INPUT_DISCORD_ID = ?,
UPDATE
    users
SET
    `nickname` = CASE
        WHEN @INPUT_NICK = ''
        or @INPUT_NICK IS NULL THEN `nickname`
        ELSE `nickname` = @INPUT_NICK
    END,
    `email` = CASE
        WHEN @INPUT_EMAIL = ''
        or @INPUT_EMAIL IS NULL THEN `email`
        ELSE `email` = @INPUT_EMAIL
    END,
WHERE
    `discord_id` = @INPUT_DISCORD_ID

The errors im getting is returned as a JSON object because me backend which is running the script is runned with node's MYSQL2 package. As I am also pretty new to SQL and have only been using basic queries before, I am a bit unsure what part of the query is causing the syntax errors.

{
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @INPUT_EMAIL = '[email protected]', SET @INPUT_DISCORD_ID = '322015089529978880' at line 1",
  sql: "SET @INPUT_NICK = 'test', SET @INPUT_EMAIL = '[email protected]', SET @INPUT_DISCORD_ID = '322015089529978880', UPDATE users SET `nickname` = CASE WHEN @INPUT_NICK = '' or @INPUT_NICK IS NULL THEN `nickname` ELSE `nickname` = @INPUT_NICK END, `email` = CASE WHEN @INPUT_EMAIL = '' or @INPUT_EMAIL IS NULL THEN `email` ELSE `email` = @INPUT_EMAIL END, WHERE `discord_id` = @INPUT_DISCORD_ID"
}

How would I achieve this?

CodePudding user response：

UPDATE users
SET nickname = COALESCE(NULLIF(?, ''), nickname),      -- INPUT_NICK
    email = COALESCE(NULLIF(?, ''), email)             -- INPUT_EMAIL
WHERE discord_id = ?;                                  -- INPUT_DISCORD_ID