Udate Query

gregalb · April 19th, 2007, 09:14 PM

Hello All

I want to write a query that will update a field "A" based on weather another field "B" contains something in field "C"

Eg:

Valuation_no "B" rate_code_rate "C" origen_rate_code "A"
03710/003.00 2000 02000
03710/003.00 3020 03020

Basically i want to say that if "valuation_no" contains "rate_cade_rate" of 2000 and 3020 then make "origen_rate_code" =02000 where "rate_code_rate" = 2000 and the same for where "rate_code_rate" = 3020 make "origen_rate_code" = 03020.

Thanks

sshelper · April 20th, 2007, 08:51 AM

Try it this way:

UPDATE YourTable
SET origen_rate_code = RIGHT('00000' + rate_code_rate, 5)
WHERE valuation_no LIKE '%' + rate_code_rate + '%'

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

gregalb · April 22nd, 2007, 03:46 PM

Hi

Thanks for the reply but it did not do as i want (I probably did not explain properly). What I need to do is where a valuation no has a rate_code_rate of 2000 and 3020, then make origen_ _code = 02000 (for rate_code_rate of 2000 and = 03020 (for rate_code_rate of 3020)

Thanks

happygv · April 23rd, 2007, 01:15 AM

Then try this.

Code:

UPDATE <YourTable>
SET origen_rate_code = RIGHT('00000' + rate_code_rate, 5)
WHERE rate_code_rate in (2000, 3020)

Cheer

_________________________
- Vijay G
Strive for Perfection

gregalb · April 23rd, 2007, 03:39 PM

Thanks Vijay

How would I write this query if there were valuation no's that had a rate_code_rate of 1000, 2000 and 3020 but i only wanted to make origen_rate_code = 02000 and 03020 where valuation no only had 2000 and 3020 and not 1000, 2000 and 3020?

Thanks for your reply it did work

Greg

happygv · April 26th, 2007, 09:26 AM

Sorry for the delayed response.

The update statement I posted above does that. It checks only for 2000 and 3020 and updates only those. So I believe it is taken care of.

cheers.

_________________________
- Vijay G
Strive for Perfection

gregalb · April 26th, 2007, 09:56 PM

Hi Vijay

Thanks for the reponce. Your script does work but this is my problem. some valuation no has only got rate_codes of 2000 and 3020 and some have 1000, 2000 and 3020. I only want to change those where a valuation no has 2000 and 3020. here is a copy of my extract

valuation_no rate_code origen_rate_code
03710/001.00 123 1230
03710/001.00 3020 123
03710/001.00 2000 123
03710/001.00 1000 123
03710/002.03 2000 123
03710/002.03 3020 123
03710/002.03 401 4010
03710/002.03 1000 123
03710/003.00 2000 123
03710/003.00 3020 123

Where the origen_rate_code = 123 for valuation no containing 1000,2000,3000 I want to keep but only change to 02000 and 03020 where valuation no only has 2000,3020and not 1000

Thanks

Greg

Peso · April 27th, 2007, 12:24 AM

Use CASE operator.

happygv · April 27th, 2007, 07:48 AM

Try this...

Code:

UPDATE test
SET origen_rate_code = RIGHT('00000' + rate_code_rate, 5)
 WHERE valuation_no NOT IN 
( SELECT valuation_no FROM test WHERE rate_code_rate IN (1000) )
AND rate_code_rate in (2000, 3020)

Just added the one marked in BLUE

Cheers
_________________________
- Vijay G
Strive for Perfection

gregalb · May 2nd, 2007, 03:58 PM

Thanks Vijay this worked. Muck appreciated
Greg