Problem with SQL update

Question

0.00/5 (No votes)

See more:

I have a problem with this procedure, the update is naturally updating all the records where I only want it to update one

SQL

--checks to see if the user is signed out, if so sign them in then return 2

ELSE IF EXISTS (select * from sitestaff where SIOType=@SIOType AND SIODate=convert(date,getdate()) and Name=@name and SignIn is NULL)

BEGIN

    update sitestaff
    set
    SignIn=getdate()
    where Name=@name and SIOdate = convert(date,getdate())
    set @result=2

END

What I have tried:

I need to pass a unique column to update only one record but I'm not passing the UniqueID to the query (The database has a unique ID called ID)

Ideally needs to be :-

SQL

update sitestaff
set
SignIn=getdate()
where Name=@name and ID=ID
set @result=2

But not sure how to do this, it basically just needs to update the latest record

Posted 21-Sep-17 1:51am

caffrey_1

Updated 21-Sep-17 2:52am

v2

Add a Solution

Comments

ZurdoDev 21-Sep-17 8:05am

What are you needing help with? What is your question?

2 solutions

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)

OriginalGriff · Answer 1 · 2017-09-21T02:24:00

Unless you tell the query which ID value you want to update, you run the risk of updating multiple records - this is particularly a problem when you release to production and there are multiple uses using the DB at the same time.

If the ID is an IDENTITY field, and it was created earlier in the same connection, you can get the value by using the SCOPE_IDENTITY (Transact-SQL) | Microsoft Docs[^] function.
If it's a "real" UniqueID value (i.e. a GUID) then you will need to pass that through from wherever it was created, or you will get more problems as you go on.

caffrey_1 · Answer 2 · 2017-09-21T02:52:00

Solution 2

Think I might have solved it, simple way too

SQL

where Name=@name and SIOdate = convert(date,getdate())

should be :-

SQL

where Name=@name and SIOdate = convert(date,getdate()) and SignIn is NULL

Simple fix, the lastest record should always have a NULL SignIn

Posted 21-Sep-17 2:52am