Need SQL help
Hi Helper! I can't figure out the sql statement for the following problem: Haveing a tabel T with 3 columns( c1,c2,c3) and, say 100 rows in it. Now each row, where the c1 value is more than ones i have to set c3=1 but only of this raw has in this group c2 as the smalls value. In other terms: group t having count(c1) > 1 and update there c3=1 where c2 is min(c2) of this group. and again: Set c3 to 1 on such rows where c2 has the lowers value and c1 is not unique. Any solution? Thanks a lot and sorry for my stupid question Peter
This was a riddle in more ways that one :-)
You should be able to do it as follows :
select c1, min(c2), count(*)
from T
group by c1
having count(*) > 1
into temp temp1;
create index i1_temp1 on temp1 (c1,c2); -- this may help performance
(not logically required)
update statistics high on table temp1; -- this may help
performance (not logically required)
-- Assuming that you have a unique index on T(c1,c2) - if not, it is
possible that multiple rows with
-- the same c1,c2 combination could have their c3's set to 1.
update T
set c3 = 1
where exists ( select 1 from temp1
where T.c1 = temp1.c1
and T.c2 = temp1.c2);
HTH
Rudy
P.S. Run this on test data first.
Peter wrote:
> Hi Helper!
> I can't figure out the sql statement for the following problem:
> Haveing a tabel T with 3 columns( c1,c2,c3) and, say 100 rows in it.
> Now each row, where the c1 value is more than ones i have to set
> c3=1 but only of this raw has in this group c2 as the smalls value.
> In other terms:
> group t having count(c1) > 1 and
> update there c3=1 where c2 is min(c2) of this group.
> and again:
> Set c3 to 1 on such rows where c2 has the lowers value
> and c1 is not unique.
>
> Any solution?
> Thanks a lot and sorry for my stupid question
> Peter