CODEWITHSUNDEEP
mysqlsql
adib16
adib16

Reputation: 1697

Delete only one record in mysql database

what is the proper way to delete only one record from mysql data base. this query used for select only one record.

SELECT * FROM Customers WHERE Country='Mexico' ORDER BY Country  ASC LIMIT 1;

the above query run correctly .but when replace select to delete that not worked.

DELETE FROM Customers WHERE Country='Mexico' ORDER BY Country  ASC LIMIT 1;

how I can fix it?

Upvotes: 0

Views: 2496

Answers (2)

overflowed
overflowed

Reputation: 66

If you have an id column you can use a subselect. I have removed the order by since this will be the same like order by 'Mexico' asc which is pretty useless.

    DELETE FROM Customers
WHERE (CustomerID IN ( SELECT CustomerID
                  FROM Customers where country = 'Mexico'
                  ORDER BY CustomerID ASC LIMIT 1 )) ;

Upvotes: 4

ngrashia
ngrashia

Reputation: 9884

I think below query will help you. You will need to have some key ID to differentiate.

DELETE FROM Customers 
WHERE SOME_KEY_ID IN 
(
  SELECT SOME_RANDOM_ID FROM
  (
            SELECT B.SOME_KEY_ID SOME_RANDOM_ID  FROM Customers as B 
            where Country = 'Mexico'
            LIMIT 1
   )     as c 
 )  ;

Note: The inner select SOME_RANDOM_ID is required, else sqlfiddle throws errors This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery': .

Reference FIDDLE Here

Upvotes: 1

Related Questions