Help required to update SQL

SQL Apprentice Question
have a table named Car with the field name as Position. I want to
update the Position field. If i enter a new value as 4 for position
which already exist, then the existing value 4 and all the below items
like 5,6 and 7 must be incremented by 1

Position
1
2
3
4
5
6
7


How can this be achieved

Celko Answers
>> I have a table named Car with the field [sic] name as position. I want to update the position field [sic]. <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Your vague narrative is also wrong. Columns are not fields. POSITION()
s a reserved word in Standard SQL. Since you used a singular name, you
must have one car; otherwise you would have used a collective or plural
noun.



>> If I enter a new value as 4 for space_nbr which already exist, then the existing value 4 and all the below items like 5, 6 and 7 must be incremented by 1 <<


Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" , "below" and "last" are
totally meaningless.

I am going to make a guess at what you meant. Do you have a motorpool
in which you assign parking spaces?


CREATE TABLE Motorpool
(space_nbr INTEGER NOT NULL PRIMARY KEY
CHECK (space_nbr > 0),
vin CHAR(17) NOT NULL);


Re-arrange the display order based on the space_nbr column:


CREATE PROCEDURE SwapParkingSpacees (@old_space_nbr INTEGER,
@new_space_nbr INTEGER)
AS
UPDATE Motorpool
SET space_nbr
= CASE space_nbr
WHEN @old_space_nbr
THEN @new_space_nbr
ELSE space_nbr + SIGN(@old_space_nbr - @new_pos)
END
WHERE space_nbr BETWEEN @old_space_nbr AND @new_space_nbr
OR space_nbr BETWEEN @new_space_nbr AND @old_space_nbr;


When you want to drop a few rows, remember to close the gaps with this:


CREATE PROCEDURE CloseParkingSpaceGaps()
AS
UPDATE Motorpool
SET space_nbr
= (SELECT COUNT (F1.space_nbr)
FROM Motorpool AS F1
WHERE F1.space_nbr <= Motorpool.space_nbr);


To insert a new car into the motorpool, add the new vehicle to the "end
of the line" and then swap it with the target parking space.