0

I'm using IQMS Data Dictionary and I'm trying to change a value on one table to reflect the value on another table.

update fgmulti as t1
set t1.recv_default=t2.cuser6
inner join arinvt as t2 on t1.arinvt_id=t2.id
where t1.loc_id='27053'
and t2.cuser6='Y'

I've tried to move the Set command line around like:

update fgmulti as t1
inner join arinvt as t2 on t1.arinvt_id=t2.id
set t1.recv_default=t2.cuser6
where t1.loc_id='27053'
and t2.cuser6='Y'

I'm still getting the error of a missing Set Keyword.

What am I doing wrong?

So I tried many of the examples from the linked question that was similar to this one. Below is one example, but now I'm getting another error. I've verified that this is an actual value on our database.

ORA-00904: "TABLE2"."VALUE": invalid identifier

Update table1 a Set a.DEFAULT= (Select table2.VALUE
                                  from table2 b
                                  where a.table2id=b.id)
Where a.LOCATION = '27053'
and exists (select b.VALUE
        from b
        where a.table2id=b.id)
Cœur
  • 37,241
  • 25
  • 195
  • 267
Teeke
  • 1
  • 1
  • Hi Teeke, - your question has been closed as a duplicate of a similar question. That may be OK (I will not override it) - the linked question will show you some options for updating through a join in Oracle. But your *current* error is caused by something else: There should be no keyword **as** in front of a table alias in Oracle. Delete the word **as**, now you will receive a different error (for the update of a join using invalid syntax), then follow the link to the duplicated question. –  Jan 29 '18 at 16:38
  • I'm still getting an error coming back about not ending the command properly after trying many of the options on the linked question. Here's another one I've tried and it doesn't seem to change. 'merge into fgmulti t1 using arinvt t2 on (t1.arinvt_id=t2.id) when matched then update set t2.recv_default=t1.cuser6 where t1.loc_id='27053' and t2.cuser6='Y' ;' – Teeke Jan 29 '18 at 17:13
  • On line 2, you aliased the table `table2` to `b`, so you have to call it `b.VALUE` now in line 1. – kfinity Jan 29 '18 at 19:58

0 Answers0