Hayra
Hayra

Reputation: 466

SQL update only one ROW with in clause in ORACLE

I know how to use SQL but have some specific requirement to do from myself right now and i am a bit confused.

I have a table that has documentnumber column and here is the table information simply :

Documentnumber VIB       Status
1              TAS7002   OK
1              TAS7003   OK
1              TAS7004   OK
2              TAS7002   OK
2              TAS7003   OK
2              TAS7004   OK

What i want to update only one row, foreach different documentnumber which is in (1,2). Example could be : Update documentnumber=1 and VIB = TAS7002 && documentnumber=2 and vib=TAS7002 It can be randomly choosen about which vib number should be updated, can be any one of them(TAS7002,TAS7003 etc.), but i would like to put this query into one query. The reason is that i have documentnumbers as a list but not vib numbers...

What i've tried is that :

update bshm_sp_pidok_objlink
set status = 'NEW'
where documentnumber in
(1,2)
and status = 'OK'
and rownum = 1;

Can you please check what i am doing wrong ?

Upvotes: 0

Views: 2826

Answers (4)

VIDHYA
VIDHYA

Reputation: 11

Kindly use ROWID to retrieve the unique ID and update the values . Eg :

Create table aa ( doc_no varchar2(100),  
       docname varchar2(100),status varchar2(100));

Insert into aa values ( '1','VID11111','OK');
Insert into aa values ( '1','VID11112','OK');
Insert into aa values ( '1','VID11113','OK');
Insert into aa values ( '1','VID11114','OK'); 
Insert into aa values ( '1','VID11115','OK');

Insert into aa values ( '2','VID22221','OK');
Insert into aa values ( '2','VID22222','OK');
Insert into aa values ( '2','VID22223','OK');
Insert into aa values ( '2','VID22224','OK'); 
Insert into aa values ( '2','VID22225','OK');

Commit;

Select * from aa  ;
Update aa set status = 'New' 
Where rowid in 
( select min(rowid ) from aa group by doc_no);

Regards, Vidhya D

Upvotes: 0

Vivek
Vivek

Reputation: 803

update bshm_sp_pidok_objlink
set status = 'NEW'
where (documentnumber,status,VIB) IN 
    (select documentnumber,status,VIB 
       from bshm_sp_pidok_objlink
     where documentnumber in (1,2)
       and status = 'OK'
       and rownum = 1);

EDIT-1

It will update the random one row from each of group, give a try:

UPDATE bshm_sp_pidok_objlink 
SET 
    status = 'NEW'
WHERE
    rowid IN (SELECT 
            rid
        FROM
            (SELECT 
                documentnumber, MIN(rid) rid
            FROM
                (SELECT 
                documentnumber, FIRST_VALUE(rowid) over (order by DBMS_RANDOM.VALUE) rid
            FROM
                bshm_sp_pidok_objlink
            WHERE
                documentnumber IN (1 , 2)
                    AND status = 'OK')
            GROUP BY documentnumber))

Upvotes: 0

mamilo
mamilo

Reputation: 11

update bshm_sp_pidok_objlink
set boogle_status = 'FOO'
where (dokar, rowid) in ( 
    select  dokar, min(rowid) 
    from bshm_sp_pidok_objlink
    where dokar in ('ARA','AGA')
           and boogle_status = 'NEW'
    group by dokar
);

Upvotes: 1

user330315
user330315

Reputation:

You could use a subquery, that picks one row for each documentnumber:

update bshm_sp_pidok_objlink
  set status = 'NEW'
where (documentnumber, vib) in (select documentnumber, min(vib) 
                                from bshm_sp_pidok_objlink
                                where documentnumber in (1,2)
                                  and status = 'OK'
                                group by documentnumber);

Upvotes: 3

Related Questions