How to identify record is not updated in SQL server

Question

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 26-Aug-21 22:59pm

prasanna204

Add a Solution

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[^]

prasanna204 27-Aug-21 5:30am

I am manually increasing sno count max(sno)+1 like this

Wendelius 27-Aug-21 6:09am

As already pointed out, don't. Its a very bad way and leads to more and more problems. Instead, use IDENTITY. That's the way SQL server is supposed to be used.

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)