SQLTeam.com | Weblogs | Forums

Basic update command


#1

Hi,

I need to update the columns below using the 2 byte postcodes below where they match the original DB postcode but will need a wildcard as I matching a 2byte postcode against a full postcode.

sorry I now its pretty basic but appreciate yur help! :slight_smile:

-- Postcode regions
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_Sout_East] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_North_East] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_North_West] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_East_Midlands] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_West_Midlands] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_South_West] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_N_Ireland] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_Scotland] varchar (50) NULL;
ALTER TABLE [Matching]..[Cafod catholic] ADD [x_Wales] varchar (50) NULL;

postcodes

SOUTH EAST
al,bn,cm,ct,gu,hp,lu,me,mk,ox,po,rg,rh,sg,sl,so,ss,tn
GREATER LONDON
br,cr,da,e,ec,en,en,ha,ig,kt,n,nw,rm,se,sm,sw,tw,ub,w,wc,wd
NORTH EAST
Dh,dl,hg,hu,ls,ne,sr,ts,wf,yo
NORTH WEST
Bb,bd,bl,ca,ch,cw,fy,hd,hx,l,la,m,ol,pr,sk,wa,wn
EAST MIDLANDS
Cb,co,de,dn,ip,le,ln,ng,nr,pe,s


#2

Can you please post some source data (not just the prefixes) and desired results after update?


#3

Hi
the source data is like the below, so you will see my query only takes the first two character's of the postcode then I need a wildcard. the idea is to update the relevant geo column with "Y" where the postcodes match!

"address1","address2","address3","address4","address5","postcode","North east","North West","South east"
"xxxxx","Abbeydale","Gloucester",,,"GL4 5XP",,,
"xxxxx","Abbeymead","Gloucester",,,"GL4 5WP",,,
"xxxxx","Abbeymead","Gloucester",,,"GL4 5TU",,,
"xxxxx","Abbeymead","Gloucester",,,"GL4 5TA",,,
"xxxxx","Abbeymead","Gloucester",,,"GL4 5FY",,,


#4

OK -- now are you looking to hard-code the prefixes or pull them from a ref table?


#5

just want to update the relevant column e.g. South east in the original database where the postcodes match

hope this is the correct answer your looking for