CODEWITHSUNDEEP
phpmysqlsql
user2854563
user2854563

Reputation: 268

Delete X rows from table

I want to delete X rows from a table having an equal column values. For a better way of this question, I'm writing this question in a sql query way, please understand:

DELETE id FROM table_name WHERE column_value = 1 AND user_id = 2;

And this query should delete 3 rows. How can I achieve this?

Upvotes: 0

Views: 508

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

One can use the LIMIT keyword (according to the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/delete.html):

DELETE FROM table_name WHERE column_value = 1 AND user_id = 2 LIMIT X;

Where you replace X with a number.

EDIT: (regarding the comment)

I copied this from the specifications, looks like it solves the problem you are trying to tackle:

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

LIMIT is a MySQL specific instruction, you cannot use it as a generic SQL query, other databases like PostgreSQL might fail to execute such query.

Another remark: you specified id in the query. If you remove rows, you should not specify columns.

I've tested it using SQL Fiddle and it succeeded: http://sqlfiddle.com/#!2/13ccb7/1/0 and http://sqlfiddle.com/#!2/258203/1/0.

Upvotes: 2

Related Questions