Re: Newbie SQL update script query
Date: Wed, 11 Jan 1995 15:51:08 -0500
Message-ID: <l.carl.pedersen-1101951551080001_at_kip-2-sn-53.dartmouth.edu>
In article <l.carl.pedersen-1001951912030001_at_kip-2-sn-53.dartmouth.edu>, l.carl.pedersen_at_dartmouth.edu (L Carl Pedersen) wrote:
>i might find it easier to understand your problem if you did not
>consistently misspell "occurrence". please fix this.
>
>you might try something like:
>
>update TableA a set a_status = 'W'
> where 'W' =
> ( select max(b_status) from TableB
> where b_s_ref = a_s_ref and
> b_c_ref = a_c_ref and
> b_status != 'X' and
> b_end =
> (select max(b_end) from TableB
> where b_s_ref = a_s_ref and
> b_c_ref = a_c_ref and
> b_status != 'X') );
>
>i haven't tested this, but it should work with a little fiddling -
>depending on your answers to my other questions.
>
>max() is a dirty trick. you could make it work regardless of the ordering
>by using:
>
> max(decode(b_status,'W','W'))
>
>this is even trickier, but not dependent on the ordering.
- original poster wrote:
>If we change the test to
>where 'W' = ( select min(b_status)... etc
>I think this would work.
right. i think that's where i was headed, but i got distracted. sorry. this is still dependent on the lucky fact that 'S'<'W', but you can get around that with something like:
where 1 = (select max(decode(b_status,'W',1,2)) ...
or alternatively,
where 2 = (select min(decode(b_status,'W',2,1)) ...
...the idea being that if there is anything *except* a 'W' in the result of the query then the max or min will find it and cause the comparison to be false.
---Received on Wed Jan 11 1995 - 21:51:08 CET
>The only outstanding problem is if these subqueries return zero rows
>due to all the occurrences ( <- see I'm learning ) being mistakes ( b_status
>='X' ) but I should be able to deal with this myself with an nvl function
>here and there.
--- end of quoted material --- i don't think you need anything extra to account for this. MAX and MIN will return null if there are no rows that meet the cirteria - which there won't be if they are all 'X'. 'W' = null will be false. x=null is false for any x, even if x is null. this is why i suggested using the comparison to the result of an aggregate in the first place. it avoids the need to worry about what happens when you have a query that returns no rows. [sorry about the mess folks. those who saw the original post should be able to fogure this out. those who didn't need not care. should have done the whole thing via email alone. i'll try to be more careful.]