Incorrect syntax near the keyword 'or'

Transact-SQL
1

Hi,

could someone tell me where I'm going wrong, keep getting the error message "Incorrect syntax near the keyword 'or'."
thanks

update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_Sout_East] = 'Y' where [POSTCODE] like 'al%' or [POSTCODE] like 'bn%' or [POSTCODE] like 'cm%' or [POSTCODE] like 'ct%' or [POSTCODE] like 'gu%' or [POSTCODE] like 'hp%' or [POSTCODE] like 'lu%' or [POSTCODE] like 'me%' or [POSTCODE] like 'mk%' or [POSTCODE] like 'ox%' or [POSTCODE] like 'po%' or [POSTCODE] like 'rg%' or [POSTCODE] like 'rh%' or [POSTCODE] like 'sg%' or [POSTCODE] like 'sl%' or [POSTCODE] like 'so%' or [POSTCODE] like 'ss%' or [POSTCODE] like 'tn%'; or [POSTCODE] like 'hp%' or [POSTCODE] like 'lu%' or [POSTCODE] like 'me%' or [POSTCODE] like 'mk%' or [POSTCODE] like 'ox%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_north_East] = 'Y' where [POSTCODE] like 'dh%' or [POSTCODE] like 'dl%' or [POSTCODE] like 'hg%' or [POSTCODE] like 'hu%' or [POSTCODE] like 'ls%' or [POSTCODE] like 'ne%' or [POSTCODE] like 'sr%' or [POSTCODE] like 'ts%' or [POSTCODE] like 'wf%' or [POSTCODE] like 'yo%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_north_west] = 'Y' where [POSTCODE] like 'bb%' or [POSTCODE] like 'bd%' or [POSTCODE] like 'bl%' or [POSTCODE] like 'ca%' or [POSTCODE] like 'ch%' or [POSTCODE] like 'cw%' or [POSTCODE] like 'fy%' or [POSTCODE] like 'hd%' or [POSTCODE] like 'hx%' or [POSTCODE] like 'L[0-9%]%' or [POSTCODE] like 'la%' or [POSTCODE] like 'm#*' or [POSTCODE] like 'ol%' or [POSTCODE] like 'pr%' or [POSTCODE] like 'sk%' or [POSTCODE] like 'wa%' or [POSTCODE] like 'wn%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_east_midlands] = 'Y' where [POSTCODE] like 'cb%' or [POSTCODE] like 'co%' or [POSTCODE] like 'de%' or [POSTCODE] like 'dn%' or [POSTCODE] like 'ip%' or [POSTCODE] like 'le%' or [POSTCODE] like 'ln%' or [POSTCODE] like 'ng%' or [POSTCODE] like 'nr%' or [POSTCODE] like 'pe%' or [POSTCODE] like 's#%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_west_midlands] = 'Y' where [POSTCODE] like 'b[0-9%]%' or [POSTCODE] like 'cv%' or [POSTCODE] like 'dy%' or [POSTCODE] like 'hr%' or [POSTCODE] like 'nn%' or [POSTCODE] like 'st%' or [POSTCODE] like 'tf%' or [POSTCODE] like 'wr%' or [POSTCODE] like 'ws%' or [POSTCODE] like 'wv%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_south_west] = 'Y' where [POSTCODE] like 'ba%' or [POSTCODE] like 'bh%' or [POSTCODE] like 'bs%' or [POSTCODE] like 'dt%' or [POSTCODE] like 'ex%' or [POSTCODE] like 'gl%' or [POSTCODE] like 'pl%' or [POSTCODE] like 'sn%' or [POSTCODE] like 'sp%' or [POSTCODE] like 'ta%' or [POSTCODE] like 'tq%' or [POSTCODE] like 'wv%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_N_ireland] = 'Y' where [POSTCODE] like 'bt%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_scotland] = 'Y' where [POSTCODE] like 'ab%' or [POSTCODE] like 'dd%' or [POSTCODE] like 'dg%' or [POSTCODE] like 'eh%' or [POSTCODE] like 'fk%' or [POSTCODE] like 'G[0-9%]%' or [POSTCODE] like 'hs%' or [POSTCODE] like 'iv%' or [POSTCODE] like 'ka%' or [POSTCODE] like 'kw%' or [POSTCODE] like 'ky%' or [POSTCODE] like 'ml%' or [POSTCODE] like 'pa%' or [POSTCODE] like 'ph%' or [POSTCODE] like 'td%' or [POSTCODE] like 'ze%'
update [Matching]..[0_24MTH_MPS_23_10_2015] set [x_wales] = 'Y' where [POSTCODE] like 'cf%' or [POSTCODE] like 'ld%' or [POSTCODE] like 'll%' or [POSTCODE] like 'np%' or [POSTCODE] like 'sa%' or [POSTCODE] like 'sy%'

2

In your first UPDATE i see ; near [POSTCODE] like 'tn%'; . Remove the ; and it should work

update [Matching]..[0_24MTH_MPS_23_10_2015] 
set [x_Sout_East] = 'Y' 
where [POSTCODE] like 'al%' or [POSTCODE] like 'bn%' 
or [POSTCODE] like 'cm%' or [POSTCODE] like 'ct%' or 
[POSTCODE] like 'gu%' or [POSTCODE] like 'hp%' or 
[POSTCODE] like 'lu%' or [POSTCODE] like 'me%' or 
[POSTCODE] like 'mk%' or [POSTCODE] like 'ox%' or 
[POSTCODE] like 'po%' or [POSTCODE] like 'rg%' or [POSTCODE] like 'rh%' or 
[POSTCODE] like 'sg%' or [POSTCODE] like 'sl%' or [POSTCODE] like 'so%' or [POSTCODE] like 'ss%' or 
[POSTCODE] like 'tn%' or [POSTCODE] like 'hp%' or [POSTCODE] like 'lu%' or [POSTCODE] like 'me%' or 
[POSTCODE] like 'mk%' or [POSTCODE] like 'ox%'
3

Thanks Man :grin: gal, was going mad!! :smile:

4

typo!! thanks mangal!!

5

Or clean it up a touch too, like this:

where 
    [POSTCODE] like 'al%' or 
    [POSTCODE] like 'bn%' or
    [POSTCODE] like 'c[mt]%' or 
    [POSTCODE] like 'gu%' or 
    [POSTCODE] like 'hp%' or 
    [POSTCODE] like 'lu%' or 
    [POSTCODE] like 'm[ek]%' or 
    [POSTCODE] like 'ox%' or 
    [POSTCODE] like 'po%' or 
    [POSTCODE] like 'r[gh]%' or
    [POSTCODE] like 's[glos]%' or
    [POSTCODE] like 'tn%'
6

Or put all of your target matches into a table and join to it:[code]create table Matches ( -- Could be a temp table if you wanted to populate it on the fly...
match varchar(10) not null
)

insert into Matches(match) -- One time activity
values
('al%'),
('bn%'),
...
('sy%')

--- Ongoing query

update m
set [x_Sout_East] = 'Y'
from
[Matching]..[0_24MTH_MPS_23_10_2015] m
inner join
Matches ms
on m.POSTCODE like ms.match[/code]This would probably be more performant but you'd want to verify that with your dataset.