Geek asks and answers

SQL Server Update does not work, however SQL says the rows were affected

I have a SQL Server 2000 database with a table named Voters. It is assumed that one column in the table ( PrecinctCodetype varchar(10)) has a 4-digit code similar to A111or D109, but some rows do matter '999'. Rows with a value '999'have the correct number in another column ( FaultCodetype varchar(50)). I want to copy values ​​from FaultCodeto PrecinctCodewhen PrecinctCode = 999.

I fulfilled this request

UPDATE Voters
SET PrecinctCode = FaultCode
WHERE (PrecinctCode = '999')

A message appears with the message “3031 rows affected”, but the affected rows still show 999 in the column PrecinctCode. I connect to a Windows authenticated database under an administrator account, so I don't think this is a security issue.

Has anyone had an update request that didn't update rows?

EDIT

I tried this query to get the schema, but nothing helped me.

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
where   TABLE_NAME like '%Voters%'

Will it just write a table design? if you can not tell me the launch request to get the necessary information? Thank.

+3
source share
3 answers

First try executing SELECT so you are sure that you are asking:

SELECT FaultCode, PrecinctCode FROM Voters WHERE PrecinctCode = '999';

NB that if the FaultCode is longer than 10 characters, your UPDATE will fail.

+1

, ,

UPDATE Voters
SET PrecinctCode = FaultCode
WHERE PrecinctCode = '999' and FaultCode <> '999'


UPDATE Voters
SET PrecinctCode = RTRIM (FaultCode)
WHERE PrecinctCode = '999' 


select *, len(FaultCode)
from Voters
WHERE PrecinctCode = '999' 
  and PrecinctCode <> FaultCode
+1

,

0
source

More articles:


All Articles