ORACLE8I: Selective UPDATE doesn't work

[JA1]

I'm having a real problem with a selective update. It used to work (I'm sure?) and now doesn't.

The database is Oracle8i, bog-standard SQL no Plus or otherwise.
Sorry it is long, but the original SELECT was long and the update has to be specific...

UPDATE
PROC_MN,
PROC_OVERVIEW,
PROC_MAJOR,
PROC_MN_CON_ACT,
PROC_MN_CON_ACT_UPD
SET
PROC_MN.LAST_REPORTED_DT=TO_DATE('28-06-2005','dd-mm-yyyy')
WHERE
PROC_MN.PROC_MN_SEQ IN (SELECT DISTINCT PROC_MN_SEQ FROM PROC_MN WHERE PROC_MN.PROC_MN_SEQ >= 1 ) and
PROC_OVERVIEW.PROC_OVERVIEW_SEQ IN (SELECT PROC_OVERVIEW_SEQ FROM PROC_OVERVIEW WHERE PROC_OVERVIEW.PROC_OVERVIEW_SEQ=PROC_MN.PROC_OVERV IEW_SEQ) and
PROC_MN.PROC_MN_SEQ IN (SELECT PROC_MN_SEQ FROM PROC_MN_CON WHERE PROC_MN.PROC_MN_SEQ=PROC_MN_CON.PROC_MN_SEQ) and
PROC_MN_CON.PROC_MN_CON_SEQ IN (SELECT PROC_MN_CON_SEQ FROM PROC_MN_CON WHERE PROC_MN_CON.PROC_MN_CON_SEQ=PROC_MN_CON_ACT.PROC_M N_CON_SEQ) and
PROC_MN_CON_ACT.PROC_MN_CON_ACT_SEQ IN (SELECT PROC_MN_CON_ACT_SEQ FROM PROC_MN_CON_ACT WHERE PROC_MN_CON_ACT.PROC_MN_CON_ACT_SEQ=PROC_MN_CON_AC T_UPD.PROC_MN_CON_ACT_SEQ);


ORA-00971: missing SET keyword

I've tried stipping out the tables other than PROC_MN but then the other fields in the WHERE segment become invalid...

Can anyone out there help with my syntax?

Regards,

Jane.

[jkoopmann]

As an example only.

What you basically have ( yes this is simplified )
> update t1, t2 set t1.a = 'avalue' where t1.a = t2.a;
update t1, t2 set t1.a = 20 where t1.a = t2.a
*
ERROR at line 1:
ORA-00971: missing SET keyword

but you can not have multiple tables defined to update.
what you need to do is correlate the updated table to the tables in your FROM list much like this:

> ho type 1.sql
update t1 set t1.a = 'avalue' where t1.a = (select t2.a from t2 where t1.a = t2.a)
5 rows updated


Now it was very hard to understand any correlation in your above query. almost everything is filtering out in the predicate list based on an IN list. there is no correlation between any of your tables and thus is a cartesian join condition.

In which case the only two statements in the WHERE clause that mean anything to the update would be :
WHERE PROC_MN.PROC_MN_SEQ IN (SELECT DISTINCT PROC_MN_SEQ FROM PROC_MN WHERE PROC_MN.PROC_MN_SEQ >= 1 )
and PROC_MN.PROC_MN_SEQ IN (SELECT PROC_MN_SEQ FROM PROC_MN_CON WHERE PROC_MN.PROC_MN_SEQ=PROC_MN_CON.PROC_MN_SEQ)

if this is the case then the following UPDATE statement would work:

UPDATE PROC_MN
SET PROC_MN.LAST_REPORTED_DT=TO_DATE('28-06-2005','dd-mm-yyyy')
WHERE PROC_MN.PROC_MN_SEQ IN (SELECT DISTINCT PROC_MN_SEQ FROM PROC_MN WHERE PROC_MN.PROC_MN_SEQ >= 1 )
and PROC_MN.PROC_MN_SEQ IN (SELECT PROC_MN_SEQ FROM PROC_MN_CON WHERE PROC_MN.PROC_MN_SEQ=PROC_MN_CON.PROC_MN_SEQ)

Now I know there is probably some correlation in the other table you are trying to accomplish but you need to swithc them around to correlate back to the PROC_MN table.

hope this helps

[JA1]

Thanks for that, but the trouble is, is that the original SELECT statement selected 416 items, and the UPDATE selects 442.

Not quite the result I need!

The original SELECT statement has a convoluted WHERE section that would be so much easier if I could just transpose that to the UPDATE, unfortunately that syntax doesn't appear to be allowed.

SELECT
a.f1, a.f2, a.f3,
b.f1, b.f2,
c.f1,
d.f1, d.f2, d.f3, d.f4,
e.f2, e.f2
FROM
a, b, c, d, e
WHERE
a.f3=b.f1 AND
b.f3=c.f1 AND
c.f2=d.f1 AND
d.f5=e.f1 AND
a.f1>1

and I now need to UPDATE a.f4 so that the records from the above select statement are date-stamped.

J.

[jkoopmann]

should transpose to this:

update a set a.f4 = 'something'
where a.f1 > 1
and a.f3 = (select b.f1 from b, c, d WHERE b.f3=c.f1 AND c.f2=d.f1 AND d.f5=e.f1)

[JA1]

Thanks, that worked. I've just had to restructure the initialization of my select statement so that I can generate the update later using the same FROM and WHERE clauses

J.