SQL更新问题 [英] Problem with SQL update

问题描述

我有这个程序的问题，更新自然更新所有记录，我只想让它更新一个

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

--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

我尝试过：



我需要传递一个唯一的列来更新一条记录，但我没有将UniqueID传递给查询（数据库有一个名为ID的唯一ID）



理想情况下需要： -

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 :-

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

推荐答案

除非你告诉查询你要更新哪个ID值，否则你冒着更新多条记录的风险 - 当您发布到生产环境时，这尤其是一个问题，并且同时使用数据库有多种用途。



如果ID是IDENTITY字段，并且它是先前在同一连接中创建的，则可以使用SCOPE_IDENTITY（Transact-SQL）| Microsoft Docs [ ^ ]功能。

如果它是一个真正的UniqueID值（即一个GUID），那么你需要从它创建的任何地方传递它，否则你将当你继续下去时会遇到更多问题。

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.

想想我可能已经解决了它，简单的方法

Think I might have solved it, simple way too

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

应该是： -

should be :-

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

简单修复，最新记录应始终具有NULL SignIn

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

这篇关于SQL更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！