MySQL Update on Duplicate alternatives-CodePudding

So lets say that I have this object

let object = {
    id: 1,
    name: `John`,
    type: `student`,
};

and I want the query to insert if it doesn't exist.

await new Promise(async (resolve) => query(`INSERT IGNORE INTO \`profiles\` SET ?`, object, (e, r, f) => resolve(e ? console.log(e) : 0)));

This works totally fine and has for quiet some time, now I am looking to change that functionality and to update existing entries if it detects a duplicate, for instance lets say that "John" is no longer a "student" but is now an "alumni" so the object would now be:

let object = {
    id: 1,
    name: `John`,
    type: `alumni`,
};

but since id is "UNIQUE" on the database, it will "IGNORE" it and not update it and you can't add "ON DUPLICATE KEY UPDATE" to the end (at least not from what I have tried).

I'd like it to be able to update it only if the object is different instead of updating every time it runs but I feel I will have to do a "SELECT" first and that is more taxing than just updating. But if anyone knows a way to do that, please advise.

Any pointers would be helpful.

CodePudding user response：

You should certainly be able to use ON DUPLICATE KEY UPDATE for this. If the new values are the same as the old values, nothing is changed.

await new Promise(async (resolve) => query(`
    INSERT INTO profiles
    SET ? 
    ON DUPLICATE KEY UPDATE name = VALUES(name), type = VALUES(type)`, object, (e, r, f) => resolve(e ? console.log(e) : 0)));

VALUES() is used to get the value that would have been inserted if not for the duplicate key.

CodePudding user response：

So I ended up doing this so I wouldn't have to list out every item in the object, if someone wants to recommend a prettier version, that is fine, but this is working for me.

let object = {
    id: 1,
    name: `John`,
    type: `alumni`,
};

let newString = ``;
let lastKey = Object.keys(object).pop();

for(let k in object){
    let key = `\`${k}\``;
    let value = typeof newData[k] === `string` ? `"${newData[k]}"`: newData[k];

    newString  = `${key} = ${value}${k === lastKey ? `` : `, `}`;
}

await new Promise(async (resolve) => query(`INSERT INTO \`table\` SET ${newString} ON DUPLICATE KEY UPDATE ${newString}`, (e, r, f) => resolve(e ? console.log(e) : 0)));

This allowed the data to remain true to its form as well if any of the values were not a string (perhaps a boolean or number) and since you can't end a SQL query with a comma, the newString checks the key to see if it is the last one based on its name (not my preferred way to do it but it works)