CODEWITHSUNDEEP
mysqlsql
nullException
nullException

Reputation: 1122

mysql delete if exists

how can I delete if the row exists like this select statement. I tried replacing select with delete but it didn't work. I a need statement that deletes and return 1 if the row exists. Or it returns 0 if the row is not found in the table.

SELECT IF ( EXISTS (SELECT * FROM `cancellation` WHERE `ID`=2), 1, 0)

Upvotes: 0

Views: 11503

Answers (2)

Ashish Mishra
Ashish Mishra

Reputation: 1

Delete query always returns
0 --> when record is not deleted
1 --> at successfull delete
like:
String sql="delete from marks where id=101;
flag=stmt.executeUpdate(sql);

Upvotes: -2

Tony
Tony

Reputation: 2754

You should execute two statements. First, your delete:

DELETE FROM `cancellation` WHERE `ID` = 2

To get a count, follow your DELETE immediately with this:

SELECT ROW_COUNT()

Anything greater than 0 will be a successful delete.

Update: Three-value logic in PHP

If you're using PHP and want to use some trickery, use a null value to indicate failure of the DELETE process. PHP's mysqli_query will return false if the delete operation failed, so we can use that to force a NULL return value. 

<?php
   function zapper ($row_id) {
      if ($result = mysqli_query($connection, "DELETE FROM `cancellation` WHERE `ID` = " . $row_id) === TRUE) {
         if (mysqli_affected_rows($connection) > 0) {
            // delete successful - some rows were deleted
            return TRUE;
         } else {
            // delete successful - no rows were deleted
            return FALSE;
         }
      } else {
         // delete unsuccessful
         return NULL;
      }
   }
?>

Upvotes: 3

Related Questions