Update

Q: Update an existing table by adding a column, with a specific order?

I had a table(nd_osp_outcome) with data like -

OSP_ID ID DESCRIPTION RESPONSIBILITY

--- ------ ----------- ---------------

101 100 ddd rr

101 101 ddd rr

250 250 ddd rr

250 251 ddd rx

250 252 ddd rp

3100 3100 ddd rt

3100 3101 ddd ry

Have to update this like -

OSP_ID ID DESCRIPTION RESPONSIBILITY IDX

--- ------ ----------- --------------- ------

101 100 ddd rr 0

101 101 ddd rr 1

250 250 ddd rr 0

250 251 ddd rx 1

250 252 ddd rx 2

3100 3100 ddd rt 0

3100 3101 ddd ry 1

--

So, I am creating a temp table with the idx column -

CREATE TABLE temp_nd_osp_outcome AS

SELECT osp_id,id,rank()over ( partition by osp_id order by id) -1 idx

FROM nd_osp_outcome;

Then updating the existing table -

alter table nd_osp_outcome add idx number (10,0);

UPDATE nd_osp_outcome x

SET x.idx= (SELECT idx FROM temp_nd_osp_outcome y WHERE x.osp_id=y.osp_id AND x.ID=y.ID)

WHERE EXISTS (SELECT 1 FROM temp_nd_osp_outcome y WHERE x.osp_id=y.osp_id AND x.ID=y.ID);

select * from nd_osp_outcome order by osp_id,idx;

Q: Update records in one table based on values in another table?

alter table individual add constraint u_clnt_num_nd unique(CLNT_NUMBER_ND);

update (
select qer.INDIVIDUAL_ID, indv.id
from leg_qer qer, individual indv
where qer.client_number_nd = indv.clnt_number_nd )
set INDIVIDUAL_ID=id;

Q: How to add a Unique column on a table?

ALTER TABLE PROVIDER_SERVICE1 ADD PS_IDENTIFIER NUMBER(10);
UPDATE PROVIDER_SERVICE1 SET PS_IDENTIFIER=HIBERNATE_SEQUENCE.nextval;