Query to increase the value based on inserted value.

Question

1.00/5 (1 vote)

See more:

Dear Friends,

Can anyone guide me for this following query.

In my table i am having one column named ID(non Primary Key) which contains the values like 1,2,3,4,5 etc. If i am trying to insert 4 again that column means then new 4 should insert and old 4 should replace by 5, old 5 sholud be replaced by 6 like this orderly.

Can any one please provide the sql server query.

I need immediately.

Posted 4-Jun-13 23:21pm

Shanmugapriya N

Updated 4-Jun-13 23:46pm

v2

Add a Solution

Comments

gvprabu 5-Jun-13 5:47am

give your table structure, U need to do this in single statement or u want to use SP.
If you will keep the newly inserted value (4) in one variable then ite easy.

4 solutions

Solution 2

SQL

update temp1
set id = id + 1
where id > 3

SQL

insert into TEMP1 (ID)  values (4);

Note : Values 3 and 4 are to be replaced programmatically

Posted 4-Jun-13 23:48pm

SudhirKankal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RelicV · Accepted Answer · 2013-06-04T23:48:00

Hi ShanmugaPriya,

Try this one.

First, check if there exists any records which have ID equal or greater than 4. If so, then increment their ID values by 1. Then insert the record where its ID = 4.

SQL

BEGIN TRANSACTION TRASN_Insert
BEGIN TRY
	IF EXISTS (SELECT ID FROM TableName WHERE ID >= 4)
	BEGIN
		UPDATE TableName
		SET ID = ID + 1
		WHERE ID >= 4
	END

	INSERT INTO TableName
	(ID)
	VALUES
	(4)

	COMMIT TRANSACTION TRASN_Insert
	
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION TRASN_Insert
END CATCH

Its protected with a transaction and Try Catch for failure cases also.

Thank you,
RelicV.

Stephen Hewison · Accepted Answer · 2013-06-04T23:49:00

Solution 3

Hi,

you need to perform an update before you do the insert.

SQL along the following line would acheive this.

SQL

DECLARE @NewId INT
SET @NewId = 4

UPDATE dbo.MyTable
SET
  Id = Id + 1
WHERE
  Id >= @NewId

INSERT INTO dbo.MyTable (Id) VALUES (@NewId)

Posted 4-Jun-13 23:49pm

Stephen Hewison

Comments

gvprabu 5-Jun-13 6:05am

hi friend,
we both are have same solution :-)

gvprabu · Accepted Answer · 2013-06-05T00:03:00

Solution 4

Hi ,

Try like this....

SQL

DECLARE @NewID INT 
SELECT @NewID = 4

UPDATE ID=ID+1
FROM table_Name
WHERE ID >= @NewID 

-- Insert New Record
Insert table_Name(ColumnList) values(Details) -- ID(4)

Regards,
GVPrabu

Posted 5-Jun-13 0:03am

gvprabu