Re: I hate this

Your original statement has a problem that it lacked a WHERE clause for the update command, so you guaranteed that every row in the table will be updated in some way. For the rows with an 11-character value, the subquery returned a NULL because it couldn't find a matching row with a 9-character value. This is the documented behavior of subqueries.

If you really need that strange, self-joining correlated subquery because of the stuff you decided NOT to reveal to the great unwashed newsgroup, then just make sure you add a where clause to only update the rows that need updating!

Martin

Martin Doherty wrote:

> You're right, it is simple.
>
> update bgt.bgt2
> set labno = '19' || labno
> where length(trim(labno)) = 9
>
> hth
> Martin Doherty
>
> bgt0990 wrote:
>
>> What should be a simple update is driving me bonkers!
>>
>> I am trying to add the 2 digit year prefix into an existing table
>> field of
>> CHAR (11). This table already has old 9 digit characters/numbers
>> with a 2
>> digit date prefix portion as well as 11 digit numbers/characters.
>>
>> The update statement I'm using works on the 9 digit numbers, adding
>> the 2
>> digits nicely, however the stament nulls out any existing 11 digit
>> numbers.
>> The original statement had more logic in it but in essence this
>> statement
>> doesn't work.
>>
>> update bgt.bgt2 a
>>
>> set a.labno = (select '19'||trim(b.labno) from bgt.bgt2 b
>>
>> where length(trim(b.labno))= 9 and a.labno = b.labno)
>>
>>
>>
>> Can anyone point me correctly?
>>
>> Barry
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
>> ==----------
>> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19
>> Servers =-----
>>
>>
>
Received on Fri Nov 01 2002 - 15:05:19 CST