SQL Query : IF exist delete ELSE update

Question

Good Day,

Right now Im having a hard time figuring if is it possible to have an IF ELSE function inside an sql query. I want to DELETE a row with same location and name, then UPDATE if not exist.

      $sql = "IF EXISTS (SELECT * FROM location_tbl WHERE name = '$a' && location = 'Drawing Room')
                            DELETE FROM location_tbl WHERE name = '$a' 
                                ELSE
                                INSERT INTO location_tbl (id, name, datetime, location) VALUES ('', '$a' , NOW(),'Drawing Room')";

Thank you so much for your KIND HELP

You might get some help on using `IF` statement in mysql here: https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query — Niranjan Rajawat, Dec 03 '17 at 03:58
@TamerShlash Hi Sir, what I mean is "IF exist - I want to delete it but if not exist I want to insert it. — The Door Keeper, Dec 03 '17 at 04:23

score 0 · Answer 1 · answered Dec 03 '17 at 04:08

You can have IF ELSE in the SELECT part of SQL, not in the way you want it.

You can do what you need in 2 SQLs - delete and then insert.

In Oracle you can use MERGE statement which can conditionally INSERT, UPDATE and DELETE. Please see https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

score 0 · Answer 2 · answered Dec 03 '17 at 04:33

0

I think using if-else is not necessary in your case. You can always delete the existing record first and then insert a new one. If the record does not exist in the table, the delete statement will not delete anything from the table.

answered Dec 03 '17 at 04:33

Dennis Cheung

49
2

score 0 · Answer 3 · answered Dec 04 '17 at 18:55

One suggestion would be to run the if statement using php:

$result = $mysqli->query("SELECT * FROM location_tbl WHERE name = '".$a."' AND location = 'Drawing Room'"); 
if($result->num_rows > 0 ){
    $mysqli->query("DELETE FROM location_tbl WHERE name = '".$a."'");
} else {
    $mysqli->query("INSERT INTO location_tbl (id, name, datetime, location) VALUES ('', '".$a."' , NOW(),'Drawing Room')");
}

SQL Query : IF exist delete ELSE update

3 Answers3