Which one is the most optimal way to delete and update query in mysql?

Question

Suppose, I need to delete or update some id informations from my database, which would be the best way to do it? Should I first find out that if that id exists or not? Or should I update the data then check the result if that comes false that means that data doesn't exist.

method 1

let find_id = "Select id from MyGuests where id=2";
if(find_id.length === 0 || find_id.length === undefined) {
      return not found //immediately exit
   }
let result = "UPDATE MyGuests SET lastname='Doe' WHERE id=2"
return result;

or should I use this one?

method 2

let result = "UPDATE MyGuests SET lastname='Doe' WHERE id=2"
 if (result === 0) {
        return not found
    }
return result;

Same question is for my delete query also? Which one is the optimal way?

You're never executing the queries. You're just testing the length of a string. — Barmar, Aug 20 '19 at 19:04
Sorry, i didn't get your answer @Barmar, Can you explain a little bit more? — Insane DA, Aug 20 '19 at 19:06
`let find_id = "Select id from MyGuests where id=2";` sets `find_id` to a string, it doesn't ever query the database. — Barmar, Aug 20 '19 at 19:07
Related: https://dba.stackexchange.com/questions/213397/is-it-worth-checking-value-with-select-before-updating — Marvin, Aug 20 '19 at 19:09
@GordonLinoff What are you referring to? Where does it check whether a database exists. It's just checking whether the row to be updated exists. — Barmar, Aug 20 '19 at 19:14

score 0 · Accepted Answer · answered Aug 20 '19 at 19:18

In general, it's best to minimize the number of queries sent to the database. So you should just perform the UPDATE or DELETE. Then you can check the affectedRows count in the result.

var sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
con.query(sql, function (err, result) {
    if (err) throw err;
    if (result.affectedRows == 0) {
        console.log("ID not found");
    }
});

Don't forget that database queries are asynchronous, you can't return a result in the normal way. See How do I return the response from an asynchronous call?

Which one is the most optimal way to delete and update query in mysql?

1 Answers1