Reputation: 466
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
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
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);
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
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
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