Click here to Skip to main content
15,559,275 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi Friends,

I am inserting records into a datatable after insertion , max(sno) lessathan rows I am deleting.

No When record is not inserting means SNO is not changing I need to delete that record immediatly.I have row created time field in the table.

how to delete record when the SNO is not changed?

What I have tried:

declare @Id int
set @Id =(select ISNULL(MAX(SNO),0) + 1 as Sno from dbo.tblInfo)
insert into dbo.tblInfo(SNO,BeaconName,BeaconUUID,MajorValue,MinorValue,RSSI,Status,CreatedDate,DeviceId) values (@Id,@BeaconName,@BeaconUUID,@Majorvalue,@MinorValue,@RSSI,'ACTIVE',getdate(),@DeviceId)
delete from tblInfo where SNO not in (select SNO from dbo.tblBeaconsInfo where CreatedDate in (select max(CreatedDate) from tblBeaconsInfo group by DeviceId))
Posted
Comments
CHill60 27-Aug-21 5:02am    
That is a very bad way to "calculate" an Id field - what if two users are using the database at the same time - they will get the same id. Use Transactions (commit/rollback) to handle problems
And if the row is not inserted there is no need to delete it!
prasanna204 27-Aug-21 5:05am    
Device id is different in the table
prasanna204 27-Aug-21 5:07am    
when SNO value is not changing that device id row should be deleted from the table
CHill60 27-Aug-21 9:43am    
Your description of the problem is not at all clear.
Richard Deeming 27-Aug-21 5:26am    
As CHill60 said, don't use MAX for an identity column. Either use the IDENTITY property on your column, or use a SEQUENCE.

IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Docs[^]
CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Docs[^]

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900