What if we only want to delete a specific number of rows?

Question

In the context of this exercise, what if we only want to delete a specific number of rows?

Answer

To delete only a specific number of rows, you can utilize the LIMIT statement. The value provided for LIMIT will be how many rows to affect.

For example, this statement will only delete the first 5 rows that match the condition,

DELETE FROM table
WHERE condition
LIMIT 5;
19 Likes

What if we only want to delete specific rows from a particular column. Without affecting the rest?

6 Likes

what do you mean with: from a particular column? If some columns have a specific value?

2 Likes

Taking this example, the LIMIT 5 command needs to supersede the WHERE condition, otherwise it will not work.
Hence, it should be:
DELETE FROM table
LIMIT 5;
WHERE condition;

This isn’t right. You can’t have 2 separate clauses like that. The WHERE clause is orphaned in this example.

9 Likes

Yeah! it works :star_struck:

SELECT arrival_airport, aircraft_code, flight_id FROM flights ORDER BY actual_departure DESC LIMIT 3;

1 Like

The phrasing made me curious, is there also a way to select the last rows for example?

3 Likes

ids are incremental, so we could order by (from high to low) you could delete the last few rows

8 Likes

You can add condition like this. Deleting rows 1 and 3 with twitter_handle is null.

DELETE FROM celebs
WHERE id in (1,3) and twitter_handle IS NULL;
15 Likes

What will happen if the table does not contain ‘ID’ column, giving that I want to delete 1st row and 3rd row in records matching the given condition?

no identifier used? The better solution would be to actually use identifiers.

Most databases retrieve the rows in the order they where inserted in (unless order by is used), so deleting the first row is simple, just use LIMIT 1, for the third row, i suppose you need LIMIT combined with OFFSET.

but honestly, its not the correct solution, its exactly the reason we use identifiers (some unique column).

5 Likes

Doesn’t work for me in SQL :frowning:

what tool are you using here? And what is the error message? I can’t read the language in the screenshot

1 Like

Error of syntaxis, ( operator is missing ) in request “…”
You can try to do it by your own. Thank you for the help!

Many thanks ,
Dan

you are using a windows specific tool, so that would be a lot of trouble.

What if you put the query on a single line? I have no experience with the tool you use

1 Like

Yeah…It works all right, Thanks :innocent: :smiling_face_with_three_hearts: …I think there are more ways to delete rows as in string slicing…like the start position and end position … Aren’t they ?

1 Like

Why do we have to use IS, why cant we use the equal sign (=)

for example

delete from celebs
where twitter_handle = null;

3 Likes

because null is a special value. null represents the absence of the value. I am sure there are more extensive articles you can find on the internet if you want a more in-depth understanding

2 Likes

I had the same question. I think this has to do with the type of value that NULL represents. NULL is a str isn’t it? Then is more accurate to search for IS than =. But in the bottom of this question lies the idea of the SQL founder I think.

null is not a string. Null is literally there what is says, there is nothing/no value. Which is why we can’t use = (which check for equality).

6 Likes