Update Query Help

[ssmith001]

I have a query that selects some data based on some joins to other tables. Once I'm done selecting these rows and sending them on their merry way to another system, I need to update these records to a certain status so that I don't pick them up again the next time this runs, using the following stmt:

update stsc.recship
set UDC_LOAD_SCH_STAT = 99
where ???


The query below returns the 6 rows that I want to update:

select a.*
from stsc.recship a
, stsc.item b
, stsc.loc c
, stsc.loc d
where a.item = b.item
and ((a.source = c.loc) and c.UDC_TPORT_LOAD_SW = 1)
and ((a.dest = d.loc) and d.UDC_TPORT_LOAD_SW = 1)
and a.UDC_PLNR_APRV_SW = 1
and a.scen = 0
and a.scen = b.scen
and b.scen = c.scen
and c.scen = d.scen

I tried using 'where exists' but it ends up updating all 18K rows in the table, not just the 6.

[welchdor]

If your initial select "that selects some data based on some joins to other tables" is being done in a PL/SQL procedure or package, you could set it up in a cursor FOR UPDATE. Then, after passing your results to your other system, you could do an update WHERE CURRENT OF
cursor_name and update only the rows you selected in the cursor. This might look something like:

.
.
.
CURSOR c1 IS
SELECT......
FROM...
FOR UPDATE;

BEGIN
FOR c1_rec IN c1
LOOP
BEGIN

.
.
.
update stsc.recship
set UDC_LOAD_SCH_STAT = 99
WHERE CURRENT OF c1;
END;
END LOOP;
COMMIT;

END;
/

[ssmith001]

It's not being doine in a procedure. I could do so if necessary but I didn't want to go down that road if I didn't need to.

[jmodic]

As you don't say what the primary key for the table RECSHIP is I'll use ROWID to uniquely identify rows in that table that need to be updated:

update stsc.recship
set UDC_LOAD_SCH_STAT = 99
where stsc.recship.rowid in
(select a.rowid as row_id
from stsc.recship a
, stsc.item b
, stsc.loc c
, stsc.loc d
where a.item = b.item
and ((a.source = c.loc) and c.UDC_TPORT_LOAD_SW = 1)
and ((a.dest = d.loc) and d.UDC_TPORT_LOAD_SW = 1)
and a.UDC_PLNR_APRV_SW = 1
and a.scen = 0
and a.scen = b.scen
and b.scen = c.scen
and c.scen = d.scen);

[ssmith001]

It works great. Thanks for your help!