+ 2

delete next row in sql

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'?

8th Apr 2021, 7:41 PM
tibi
tibi - avatar
6 RĂ©ponses
+ 5
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.
8th Apr 2021, 7:51 PM
Jerry Hobby
Jerry Hobby - avatar
+ 3
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.
8th Apr 2021, 8:23 PM
Jerry Hobby
Jerry Hobby - avatar
0
Jerry Hobby Basically I want to delete the first 'row' after the name john. The id and year doesn't matter.
8th Apr 2021, 8:10 PM
tibi
tibi - avatar
0
U can write command in this way-- Delete from <table_name> where id between 3 and 11;
9th Apr 2021, 7:08 AM
born2code
born2code - avatar
0
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
24th Jul 2021, 10:07 PM
Hansley LOVINCE
Hansley LOVINCE - avatar
0
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')
10th Aug 2021, 5:48 AM
Pablo Quintana
Pablo Quintana - avatar