I have been running the following query and it has been working fine. Today I ran it and I get an error: Invalid Column name 'Duplicate_Record'. That column does exist and if I do select duplicate_record from wce_contact I get no error. I have clear the cache but that did nothing, any ideas would be very helpful thanks.
update dt
set Duplicate_Record = '1'
from
(select row_number() over (partition by emailaddress order by uniqueid) as rnum, emailaddress from wce_contact where Duplicate_Record is null) dt
where rnum > 1
I would have thought that [Duplicate_Record] needs to one of the SELECT columns in the inner SELECT (you have [emailaddress] there, which is not used in the Outer Select), but "if its been running fine" then unless something changed it seems unlikely that is the problem.
In case it helps I code a "Flag Duplicates Update" a bit differently:
;WITH CTE_Duplicates
AS
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER (
PARTITION BY emailaddress
ORDER BY uniqueid
)
, Duplicate_Record
FROM wce_contact
WHERE Duplicate_Record is null
)
UPDATE U
SET Duplicate_Record = '1'
FROM CTE_Duplicates AS U
WHERE T_RowNumber >= 2
FWIW if it was me I would have [Duplicate_Record] as a BIT field, not allow NULLs, and thus the only possible values would be 0 / 1. That would save some space compared to a CHAR column. But you might be storing things in that column other than '1' ...
Recommend you prefix the table with its schema (probably "dbo") so
FROM dbo.wce_contact
Its a slim chance, but IF the current user has ALSO got a table called wce_contact in their DEFAULT SCHEMA (and assuming that their default schema is NOT "dbo" ...) then you would get exactly this type of effect.
Supposedly SQL is more efficient if all objects are referenced with their schema. Might be splitting hairs though ... but certainly if there is ever a second, or more, schema NOT having the schema explicitly named is likely to become a nuisance.
I've never needed to use a non-DBO schema ... but we always use the dbo. prefix ... no idea if that is overkill though
