March 11, 2009 at 7:11 am
Hello again guys,
I am trying to come up with an UPDATE statement to change the "area" and county of the following table based on the post code.
CREAT TABLE SCSite
(
Site_Num(PK, int, not null)
Site_Address(varchar(200),not null)
Site_County(varchar(20),not null)
Site_Post_Code(varchar(8),not null)
Site_Area(varchar(6),not null)
)
INSERT INTO SCSite
(Site_Num, Site_Address, Site_County, Site_Post_Code, Site_Area)
SELECT '123456','2 some street','kent','TN12 3QS', 'SOUTH', UNION ALL
SELECT '123457','2 other street','Essex','CO1 1AA', 'SOUTH'
What i want to do is run something like
UPDATE SCSite
SET Site_County = 'Kent', Site_Area ='ESEKEA'
Where Site_Post_Code LIKE 'TN12 %'
to me this looks like it would work, the only issue i have is that i have a list (stored in an excel sheet) that is 3641 rows and each one would need to be written into this code.
Any ideas?
Sam Marsden
March 11, 2009 at 7:28 am
Hi Sam
If you have a problem with Excel, tehn you cant you just import the excel into a table and then join them with the current table to do a update 😀
March 11, 2009 at 7:33 am
a little confused - sorry very new.
Sam Marsden
March 11, 2009 at 7:41 am
This is what I would do: create a (temporary) table with postal codes and correct county and area names, and update from there. It will come out like this:
CREATE TABLE #Counties
(
Site_County varchar(20) not null,
Site_Post_Code varchar(8) not null,
Site_Area varchar(6) not null
)
INSERT INTO #Counties
(Site_County, Site_Post_Code, Site_Area)
SELECT 'Kent','TN12 3QS', 'ESEKEA'
UPDATE SCS
SET Site_County = Co.Site_County, Site_Area = Co.Site_Area
FROM SCSite SCS
INNER JOIN #Counties Co ON Co.Site_Post_Code = SCS.Site_Post_Code
SELECT * FROM SCSite
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 11, 2009 at 7:45 am
oh ok i see the logic there - the only issue i can see is that i dont have a definative list of all of the post codes - i only have the outbound (i.e TN12, BS27 etc..)
Sam Marsden
March 11, 2009 at 9:16 am
sam.marsden (3/11/2009)
oh ok i see the logic there - the only issue i can see is that i dont have a definative list of all of the post codes - i only have the outbound (i.e TN12, BS27 etc..)
Ok, in that case, change it to this:CREATE TABLE #Counties
(
Site_County varchar(20) not null,
Site_Post_Code varchar(8) not null,
Site_Area varchar(6) not null
)
INSERT INTO #Counties
(Site_County, Site_Post_Code, Site_Area)
SELECT 'Kent','TN12', 'ESEKEA'
UPDATE SCS
SET Site_County = Co.Site_County, Site_Area = Co.Site_Area
SELECT *
FROM SCSite SCS
INNER JOIN #Counties Co ON Co.Site_Post_Code = LEFT(SCS.Site_Post_Code,PATINDEX('% %',SCS.Site_Post_Code)-1)
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 11, 2009 at 9:23 am
Added to my last post:
I don't know the english postal code very well, and also not your data of course. So I only suggest all your postal codes have a space after the outbound code. If this may not always be the case, and the outbound is always 4 characters, you might change the PATINDEX() -1 part to simply 4.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 11, 2009 at 9:29 am
and this would work bearing in mind that the table to be changed had approx 56,000 rows and the post code table would have approx 3500 rows?
Sam Marsden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply