-2
SELECT  arinvt.buyer_code_id
FROM arinvt
LEFT JOIN arinvt_vendors
ON arinvt.ID = arinvt_vendors.ARINVT_ID
where arinvt_vendors.vendor_id like 61690 
and arinvt_vendors.is_default ='Y' 
and arinvt.buyer_code_id is null

update arinvt 
set arinvt.buyer_code_id = 66 
SELECT arinvt.buyer_code_id 
FROM arinvt 
LEFT JOIN arinvt_vendors 
ON arinvt.id = arinvt_vendors.arinvt_id 
(where arinvt_vendors.vendor_id like 61690 
and arinvt_vendors.is_default ='Y' 
and arinvt.buyer_code_id is null)

SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:

< *Action:

I looked at the other (duplicate) question before I posted mine and tried this Update:

UPDATE ( SELECT arinvt.buyer_code_id FROM arinvt LEFT JOIN arinvt_vendors ON arinvt.id = arinvt_vendors.arinvt_id where arinvt_vendors.vendor_id like 61690 and arinvt_vendors.is_default ='Y' and arinvt.buyer_code_id is null )x SET arinvt.buyer_code_id = 66

and I get:

Error report - SQL Error: ORA-00904: "ARINVT"."BUYER_CODE_ID": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

I'm not sure what I'm doing wrong. Can someone help please, we need this to fix our production system.

122 Neo
  • 11
  • 1
  • 1
    Fails how? Error? Wrong data updated? – takendarkk Mar 17 '17 at 02:38
  • 2
    Please dont add code or other useful information in the comments. EDIT your question and provide it there. Also, only code doesnt help much. Clarify what you want to do – Gurwinder Singh Mar 17 '17 at 02:38
  • Oracle does not support ANSI 92 joins in UPDATE or DELETE statements. I agree it would be neat if they did. The answers in the linked thread will give you solutions which will work in Oracle – APC Mar 17 '17 at 06:29

1 Answers1

0

You have a syntax error here:

arinvt_vendors.is_default 'Y'

I'm guessing you probably meant

arinvt_vendors.is_default = 'Y'
pmorken
  • 764
  • 4
  • 11
  • Can you post the UPDATE query that fails with ORA-00933? – pmorken Mar 17 '17 at 03:19
  • update arinvt set arinvt.buyer_code_id = 66 (SELECT arinvt.buyer_code_id FROM arinvt LEFT JOIN arinvt_vendors ON arinvt.id = arinvt_vendors.arinvt_id where arinvt_vendors.vendor_id like 61690 and arinvt_vendors.is_default ='Y' and arinvt.buyer_code_id is null) – 122 Neo Mar 17 '17 at 03:24
  • Assuming the 66 is a typo the syntax looks OK. I noticed that it seems like you want to select the non-null buyer_code_id to do the update though from the inner query (e.g. SELECT arinvt_vendors.buyer_code_if FROM ...) – pmorken Mar 17 '17 at 03:39