Update Field based upon Value of another Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Field based upon Value of another Field

Author

Topic

1ststop
Starting Member

2 Posts

Posted - 2008-12-12 : 04:30:43

Hi

I need to write a query that does the following:

In the UK we have postcodes such as M41 7YH or SK14 2AR.

I have to update a field with either TRUE or FALSE if it falls within a postcode sector:

The Sectors are M417 or SK142 etc, so I need to find the space in the postcode and join the characters before the space and the first character after the space together and see if it is in the list of postcodes. This list is not the in database but could be.

Any help would be greatly appeciated.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 05:12:22

This function partially answers your problem.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108733&SearchTerms=postcode

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 05:14:09

you could use replace function to get rid of spaces.

select replace('M41 7YH ',' ','')

1ststop
Starting Member

2 Posts

Posted - 2008-12-12 : 05:22:01

Hi

We cannot update the post code field as its used for identiity verification, so would we need to creat a new field?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 05:36:22

quote:
Originally posted by 1ststop

Hi

We cannot update the post code field as its used for identiity verification, so would we need to creat a new field?

Need not update post fields. I don't know the exact structure of your tables. Regardless, your query should be something like

UPDATE A 
SET A.TRUEFALSECOLUMN=CASE WHEN B.POSTCODECOLUMN IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END
FROM
YOURTABLE A LEFT JOIN LISTOFPOSTCODESTABLE B  ON REPLACE(A.POSTCODECOLUMN,' ','')=B.POSTCODECOLUMN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:18:05

slight modification ( as you're interested only on part of actual post code value


UPDATE A 
SET A.TRUEFALSECOLUMN=CASE WHEN B.POSTCODECOLUMN IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END
FROM YOURTABLE A 
LEFT JOIN LISTOFPOSTCODESTABLE B  
ON LEFT(A.POSTCODECOLUMN,CHARINDEX(' ',A.POSTCODECOLUMN)-1)+SUBSTRING(A.POSTCODECOLUMN,CHARINDEX(' ',A.POSTCODECOLUMN)+1,1)=B.POSTCODECOLUMN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources