How can I INSERT if row doesn't exist, else UPDATE that row?

Question

At the front-end, whenever I press submit an answer to a question, it'll create 1 result_ID that has these columns.

result_ID is auto-increment, question_ID is relation with the same question_ID from questions table.

If it's the first time the user chooses the answer, it'll create an answer_result (i parse in answer_ID) and answer_checkResult (value 1 or 0 to identify it's correct or incorrect), and a history_ID to identify each record separately.

History_ID is a different table that has the quiz_ID (to identify topic) and user_ID

example: History_ID 221 has 4 questions in there, and has 4 answers with 4 answer_result.

What I don't know is how can I create a situation if the row doesn't exist, it'll run INSERT INTO situation, and else if it already exists (because the user can change the answer multiple times in 1 question), it'll UPDATE. I've just created only the INSERT INTO option, but I don't know how to do the update in this model at the same time with INSERT INTO.

This is my history_result.model that I've created, I don't know how to create an if-else to update and create at the same time...

history_result.model

const HistoryResult = function (history_result) {
    this.question_ID = history_result.question_ID;
    this.answer_result = history_result.answer_result;
    this.answer_checkResult = history_result.answer_checkResult;
    this.history_ID = history_result.history_ID;
};

HistoryResult.create = async (newHistoryResult, result) => {
    await db.query(
        `INSERT INTO history_result SET question_ID = ?, answer_result = ?, answer_checkResult = ?, history_ID = ?`,
        [
            newHistoryResult.question_ID,
            newHistoryResult.answer_result,
            newHistoryResult.answer_checkResult,
            newHistoryResult.history_ID,
        ],
        (err, data) => {
            if (err) {
                result(err, null);
                return;
            } else {
                return result(null, data);
            }
        }
    );
};

And here's how I create the history_result controller

const HistoryResult = require("../models/history_result.model");
exports.createHistoryResult = async (req, res) => {
    let { history_ID } = req.params;
    let { question_ID, answer_result, answer_checkResult } = req.body;
    let historyResult = new HistoryResult({
        question_ID: question_ID,
        answer_result: answer_result,
        answer_checkResult: answer_checkResult,
        history_ID: history_ID,
    });
    HistoryResult.create(historyResult, (err, data) => {
        if (err) {
            res.status(500).send({
                message: err.message || "Error while creating result",
            });
        }
        res.send(data);
    });
};

Is there anyways I can achieve this? Thanks.

Does this answer your question? [How to Perform an UPSERT so that I can use both new and old values in update part](https://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par) — Abra, Jun 30 '21 at 07:38
I've searched the ON DUPLICATE KEY UPDATE before, but I don't know how to use it correctly in my situation, so I don't know if I'm doing it wrong or this way doesn't apply to my situation... — Bunny, Jun 30 '21 at 07:45
[Images](//meta.stackoverflow.com/q/285551/90527) should not be used for textual data, such as DB contents. — outis, Apr 26 '22 at 08:05
Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Apr 26 '22 at 08:05

score 0 · Answer 1 · answered Jun 30 '21 at 07:52

0

Yes, you can.

but first you have to make question_ID as PRIMARY KEY. And second parameter that you pass to db.query is object that contains history_result's attributes

INSERT INTO history_result
SET ?
ON DUPLICATE KEY UPDATE
    answer_result = VALUES(answer_result),
    answer_checkResult = VALUES(answer_checkResult),
    history_ID = VALUES(history_ID)

db.query(query, objectHere, (err, data) => {
    if (err) {
        result(err, null);
        return;
    } else {
        return result(null, data);
    }
}))

answered Jun 30 '21 at 07:52

Fatur

141
9

Why I have to make question_ID as PRIMARY KEY? Because in my table I have already set result_ID as PRIMARY KEY, can you please explain more for me, because it's kinda messy the way I imagine this ON DUPLICATE KEY. – Bunny Jun 30 '21 at 07:57
@Bunny you have to create an `index` on `question_ID` with `UNIQUE` attribute, it is not necessary to make it the `PRIMARY_KEY`. You can keep your actual primary key, the duplicate check will trigger on the index – Lelio Faieta Jun 30 '21 at 07:59
But the question_ID can be repeated, if I make it unique it'll break the whole table. `question_ID` is the foreign key that I pull from the questions table. – Bunny Jun 30 '21 at 08:00
For example, the question_ID 14, at the history_ID 221, can have different answer_result and answer_checkResult, while question_ID 14 at history_ID 229, can have different answer_result and answer_checkResult, too! (in my image) – Bunny Jun 30 '21 at 08:03

ThangLeQuoc · Answer 2 · 2021-06-30T08:20:27.727

First, please read the MySQL Insert or Update on duplicate-key update tutorial,
or this Official MySQL INSERT ... ON DUPLICATE KEY UPDATE Statement document

Now back to your question. As I understand, the question_ID and history_ID pair in the history_result table would be unique, as each user will only give one answer to a question in a quiz.

First you would need to create a unique index constraints of the pair (question_ID, history_ID) of your table.

ALTER TABLE history_result
ADD CONSTRAINT uc_question_history
UNIQUE (question_ID,history_ID);

And then issue an INSERT ON DUPLICATE KEY UPDATE statement to achive the effect.

INSERT INTO history_result
    (
        question_ID, answer_result, history_ID
    )
VALUES
    (14, 21, 12)
ON DUPLICATE KEY UPDATE
    answer_result = 21;

If the question_ID = 14 and history_ID = 12 row already existed (scenario that user has already answer this question), it will trigger to update the answer_result. If not, it will insert a new record.

The DUPLICATE KEY constraint is met if a new row is a duplicate in UNIQUE index or PRIMARY KEY. In our case, it's the unique index of (question_ID, history_ID), hence the UPDATE statement will be invoked.

Sorry but in my table, there's the only result_ID is UNIQUE and are PRIMARY KEY, `question_ID`, `answer_ID`, and `history_ID` is having relation to other tables. — Bunny, Jun 30 '21 at 08:14
yes, since the `result_ID` is unique and is the primary key. So in your `INSERT ON DUPLICATE KEY UPDATE` statement, you only need to pass the `question_ID`, `answer_result` and `history_ID`. If you also include the `result_ID` in the insert into statement, the DUPLICATE KEY constraint will raised. As we only want to update if a record with the same `question_ID` and `history_ID` is already existed. — ThangLeQuoc, Jun 30 '21 at 08:18

How can I INSERT if row doesn't exist, else UPDATE that row?

2 Answers2