76982

Check if record exists delete it using mysql

Question:

i'm using MySQL and i want to check if a record exists and if it exists delete this record.

i try this but it 's not working for me:

SELECT 'Barcelone' AS City, EXISTS(SELECT 1 FROM mytable WHERE City = 'Barcelone') AS 'exists'; THEN DELETE FROM mytable WHERE City = 'Barcelone';

Thank you for your help.

Answer1:

The if statement is only allowed in stored procedures, stored functions, and triggers (in MySQL).

If I understand what you want, just do:

DELETE FROM mytable WHERE City = 'Barcelone';

There is no reason to check for the existence beforehand. Just delete the row. If none exist, no problem. No errors.

I would recommend an index on mytable(city) for performance reasons. If you want to check if the row exists first, that is fine, but it is unnecessary for the delete.

Answer2:

If you mean MySQL is returning an error message (if that's what you mean by "not working for me"), then that's exactly the behavior we would expect.

That SQL syntax is not valid for MySQL.

If you want to delete rows from a table, issue a DELETE statement, e.g.

DELETE FROM mytable WHERE City = 'Barcelone'

If you want to know how many rows were deleted (if the statement doesn't throw an error), immediately follow the DELETE statement (in the same session) with a query:

SELECT ROW_COUNT()

Or the appropriate function in whatever client library you are using.

If the ROW_COUNT() function returns 0, then there were no rows deleted.

There's really no point (in terms of MySQL) in issuing a SELECT to find out if there are rows to be deleted; the DELETE statement itself will figure it out.

If for some reason your use case requires you to check whether there are rows be be deleted, then just run a separate SELECT:

SELECT COUNT(1) FROM mytable WHERE City = 'Barcelone' <hr />

Recommend

  • Undefined index errors accessing $_POST
  • Detect if user has closed ALL windows for a website?
  • Is it possible to manually check LocateRegistry existing?
  • Table field that holds row count from another table
  • What's the point of nonfinal singleton objects in scala?
  • JPA - getting distinct value from one column
  • php regex remove digits
  • Add delivery info to query in SAP Crystal Reports
  • Update a record where _id = :id with Mongoose
  • iOS 9 custom transition - animationControllerForDismissedController not called
  • jQuery Mobile - Dialogs without changing hash
  • Mongodb update() vs. findAndModify() performace
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • Trying to get the char code of ENTER key
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • Using Sax parsing to edit and write XML in VB6
  • Debug.DrawLine not showing in the GameView
  • Xcode 4 NSLog Macro link in Xcode 3
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • Row Count Is Returning the incorrect number using RaptureXML
  • Yii2: Config params vs. const/define
  • Using jQuery closest() method with class selector
  • Adding a button at the bottom of a table view
  • Getting last autonumber in access
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Array.prototype.includes - not transformed with babel
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Statically linking a C++ library to a C# process using CLI or any other way
  • Delete MySQLi record without showing the id in the URL
  • Why winpcap requires both .lib and .dll to run?
  • GridView Sorting works once only
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • File not found error Google Drive API
  • Qt: Run a script BEFORE make
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app