查询以根据插入的值增加值。 [英] Query to increase the value based on inserted value.

问题描述

亲爱的朋友们，



任何人都可以指导我以下查询。



在我的桌子上我有一个名为ID（非主键）的列，其中包含1,2,3,4,5等值。如果我想再次插入4那列意味着新4应该插入，旧4应该替换为5，老5 sholud被这样有序地替换为6。



任何人都可以提供sql server查询。



我需要立即。

解决方案

嗨ShanmugaPriya，



试试这个。



首先，检查是否存在ID等于或大于4的记录。如果是，则将其ID值增加1.然后将记录插入其中ID = 4.

  BEGIN   TRANSACTION  TRASN_Insert 
  BEGIN  TRY 
  IF   EXISTS （ SELECT  ID  FROM  TableName  WHERE  ID> =  4 ）
  BEGIN  
 更新 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 
 结束 CATCH 

受事务保护并尝试捕获失败案件也。



谢谢，

RelicV。

  update  temp1 
  set  id = id +  1  
 其中 id>   3   

  insert   into  TEMP1（ID） values （  4  ）; 

注意：值3和4是以编程方式替换

您好，



您需要在插入之前执行更新。



沿着以下行的SQL将实现这一点。

  DECLARE   @ NewId   INT  
  SET   @ NewId  =  4  
 
  UPDATE  dbo.MyTable 
  SET  
 Id = Id +  1  
  WHERE  
 Id> =  @ NewId  
 
  INSERT   INTO  dbo.MyTable（Id） VALUES （ @NewId ）

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.

解决方案

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.

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.

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

insert into TEMP1 (ID)  values (4);

Note : Values 3 and 4 are to be replaced programmatically

Hi,

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

SQL along the following line would acheive this.

DECLARE @NewId INT
SET @NewId = 4

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

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

这篇关于查询以根据插入的值增加值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！