Skip to content
Advertisement

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

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?

Advertisement

Answer

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?

4 People found this is helpful
Advertisement