delete next row in sql

Question

Lets say I have a table like this:
------------------------
id name year
------------------------
1 john 1
3 abcd 1
6 name 2
7 john 3
8 doe 3
10 john 1
11 qwe 2

it is posible to delete or update the row that comes after the name 'john'?

Answer 1

There really isn’t a “next row” type of specifier. Well, technically there are ways. You need to form your query in such a way it only finds the data you want to modify. Is it because you only want one person per year? You can say WHERE NAME != ‘JOHN’ AND YEAR IN (SELECT YEAR FROM MYTABLE WHERE NAME = ‘JOHN’). That would give you all the years that match John’s year but not JOHN himself. So it would return “abcd” and “doe”. Get your SELECT statement to return only the rows you want to delete, then convert it to a delete statement.

Answer 2

Data in a database is not really sequential like that. There are highly discouraged methods using stored procedures that can do that, but if you think about it - there must be a reason you want to do that and that reason could probably be articulated in a query. You should look at stored procedures for your database platform. You can “walk through” row by row in a stored procedure and accomplish what you are asking. But the syntax varies for each database platform.

Answer 3

Jerry Hobby Basically I want to delete the first 'row' after the name john. The id and year doesn't matter.

Answer 4

U can write command in this way--

Delete from <table_name> where id between 3 and 11;

Answer 5

For deleting:
DELETE FROM tablename WHERE condition;
In your case:
DELETE FROM yourtable WHERE id = 3;

For updating:
UPDATE tablename SET column = value WHERE condition;

In the conditions, i prefer to use id column, but you can use any column for this

Answer 6

Hello tibi,
I can see, to do so you need to do some steps:
1. List the Id where "name = 'jhon'"
2. This entire list cannot be considered in the delete so you must anticipate that fact, making a script that goes from the inside out like this:
SELECT DISTINCT Id
FROM TABLE1 where name = 'jhon'
but all of the above is what should not be deleted so it must be within the conditions, like this:
DELETE FROM TABLE1
WHERE id NOT IN (
SELECT DISTINCT Id
FROM TABLE1
WHERE name = 'jhon')