Help required to update SQL

I 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 achievedmora wrote:
> I 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
UPDATE Car SET Position = Position + 1 WHERE Position >= 4 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TRIGGER blahblah INSTEAD OF INSERT AS
BEGIN
declare @.pos int
select @.pos = inserted.position
UPDATE table1 SET position = position+1 WHERE position >= @.pos
INSERT INTO table1 SELECT * from inserted
END
-- please verify the syntax before using - I'm just giving an idea,
don't have a server/BOL around to test & debug it.|||>> I have a table named Car with the field [sic] name as position. I want to update th
e 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 vageu 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.
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 parkging 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.