Simple query gives - Incorrect syntax near ''

Question

I have very simple query:

update dbo.myTable
    set [field1] = case when [field2] <> '' 
                     then [field1] = SUBSTRING([field2],1,6) 
                     ELSE [field1] 
                   end

Which gives:

Incorrect syntax near ''

I've already explored Incorrect syntax near '' and tried everything there.

Microsoft SQL Server Enterpris, 14.0.3294.2

...THEN [field1] = SUBSTRING([field2],1,6) ... should be ...THEN SUBSTRING([field2],1,6) ... notice the lack of [field1] = — John Cappelletti, Apr 21 '21 at 16:58
@JohnCappelletti Uff, what a silly mistake...it's taken from procedure with variables so I forgot to change that. — Hrvoje, Apr 21 '21 at 17:02

score 1 · Accepted Answer · answered Apr 21 '21 at 17:02

here is the right syntax, no need to assign it again inside the case statement:

update dbo.myTable
    set [field1] = case when [field2] <> '' 
                     then SUBSTRING([field2],1,6) 
                     ELSE [field1] 
                   end

Simple query gives - Incorrect syntax near ''

1 Answers1