1

Is it possible to update 2 columns in an Update statement that are in different tables? - The reason for the"scripted":

Where "Scripted" will be the "flag" so the formula does not run again on the same records if this field is filled in.

MERGE INTO arinvt_lot_docs ALD
USING
(SELECT arinvt.id,arinvt.class,fgmulti.in_date fgmulti.cuser3 FROM arinvt,fgmulti 
 WHERE arinvt.class LIKE 'CP%'
 OR  arinvt.class LIKE   'FG%'
 OR  arinvt.class LIKE   'IN%' 
 OR  arinvt.class LIKE   'LA%' 
 OR  arinvt.class LIKE   'PK%') Classes
ON (ALD.arinvt_id = classes.id
AND to_date(in_date) = '31-Dec-2015') --just picked a date to validate
WHEN MATCHED THEN
UPDATE SET non_conform_id = '21', fgmulti.cuser3 = 'SCRIPTED' --this text "Scripted" will fill in a field that will tell us in our reports if this was set by the script

I would like to join the tables using the arinvt.id field that is present in all 3 tables ARINVT_LOT_DOCS, FGMULTI & obviously ARINVT. ARINVT_LOT_DOCS & FGMULTI contain the NON_CONFROM_ID field that needs to be changed to '21'. The FGMULTI table also contains the CUSER3 field that would have "SCRIPTED" entered in it. The ARINVT table contains the Class of the inventory item which reflects in the conditions mentioned.

Aspiring Developer
  • 590
  • 11
  • 27
  • Not relevant to the question really, but you seem to has a cross join between arinvt and fgmulti in your using clause - there's no join condition. Was that intentional? – Alex Poole Feb 16 '16 at 18:12
  • I've heard that I could possibly need a TRANSACTION in this but I have no found precise Oracle articles describing the syntax – Aspiring Developer Feb 16 '16 at 18:16
  • I don't know what the relationship is between the tables, so don't know how you should be joining them. There is no obvious link from what you've shown, but other columns may be relevant. Maybe add the table definitions, including keys, and some sample data to the question to make it easier to see what you're trying to do. – Alex Poole Feb 16 '16 at 18:20
  • Pictures [are not preferred](http://meta.stackoverflow.com/a/285557/266304). You could just include the relevant columns, but that needs to cover those that could be used to join. – Alex Poole Feb 16 '16 at 18:47
  • Ok I just updated it with an explanation - let me know if that helps. – Aspiring Developer Feb 16 '16 at 19:35

1 Answers1

0

You cannot update two tables in one query in Oracle and other DBMS such as SQL Server but you can use transaction to achieve similar result.

This oracle community answers exactly that, if you try to join two tables, you will get this error

ORA-01776: cannot modify more than one base table through a join view

You can use transactions to update two tables in batch-like statement.

This https://stackoverflow.com/a/2044520 shows how to do it but for SQL Sever though. You need similar statement in Oracle.

Community
  • 1
  • 1
TheTechGuy
  • 16,560
  • 16
  • 115
  • 136