0

Note, this is not a duplicate question.

I have a statement I need to run in Toad. I know how to write it as an inner join update statement but Toad does not allow for inner joins in an UPDATE statement. If I write it as a SELECT statement, it works. But when I rewrite it as an UPDATE statement, Toad will not accept it. How do I write this so Toad will accept it? 

UPDATE C INNER JOIN D
ON C.SYSTEM = D.SYSTEM
SET C.REF_CD = D.CODE,
    C.REF_DT = TO_DATE('12/05/2017', 'MM/DD/YYYY')
WHERE C.CODE = '123'
AND D.CODE IS NOT NULL
AND C.CLOSED = 'N'
AND C.RCVD_DT >= TO_DATE('12/01/2017', 'MM/DD/YYYY')
AND C.RCVD_DT <= TO_DATE('12/04/2017', 'MM/DD/YYYY')
AND SUBSTR(C.SSN,7,3) >= D.FROM
AND SUBSTR(C.SSN,7,3) <= D.TO;

I have now also tried to write this as a Merge statement (which Toad allows for):

MERGE INTO C
USING D
ON (C.SYSTEM = D.SYSTEM)
WHEN MATCHED THEN
UPDATE SET C.REF_CD = D.CODE,
           C.REF_DT = TO_DATE('12/05/2017', 'MM/DD/YYYY')
   WHERE C.CODE = '123'
   AND D.CODE IS NOT NULL
   AND C.CLOSED = 'N'
   AND C.RCVD_DT >= TO_DATE('12/01/2017', 'MM/DD/YYYY')
   AND C.RCVD_DT <= TO_DATE('12/04/2017', 'MM/DD/YYYY')
   AND SUBSTR(C.SSN,7,3) >= D.FROM
   AND SUBSTR(C.SSN,7,3) <= D.TO;

This gives me a new error because system matches on multiple rows. However, it is the only matching field that I can join on.

Nina
  • 141
  • 1
  • 4
  • 12
  • 2
    What error message do you see? (It's probably not TOAD giving the error; it's oracle). Also state your oracle version – Caius Jard Dec 05 '17 at 17:23
  • [Oracle doesn't support that syntax.](https://stackoverflow.com/q/2446764/266304) Maybe you're used to that syntax from another RDBMS? It isn't Toad's fault though... – Alex Poole Dec 05 '17 at 17:30
  • Toad is a database management toolset and not a database. The error message you received is from Oracle database. – Kaushik Nayak Dec 05 '17 at 17:37
  • Not sure why this is marked as a duplicate when the one claiming to be a duplicate is completely different code. – Nina Dec 05 '17 at 17:43
  • I understand this is not the proper syntax for Toad, that's the entire purpose of my question. I've written it in a syntax that I do know and I'm hoping someone can convert it to the syntax that Toad accepts. – Nina Dec 05 '17 at 17:43
  • The error in the first statement is a syntax error because Toad does not allow JOIN statements on UPDATE statements. The error on the 2nd statement is that it is unable to get a stable set of rows. – Nina Dec 05 '17 at 17:48
  • Why isn't it a duplicate? The linked question is trying to do the same thing, and there are examples of how to modify the update statement to work. Including some using `merge`. (And, again, it ins't about syntax *Toad* accepts, it's syntax *Oracle* accepts. You also need to tell us the actual error messages you get.) – Alex Poole Dec 05 '17 at 17:51
  • "*I know how to write it as an inner join update statement*" I would be **very** interested in how you do that with Oracle. Do you have access to a future Oracle version? –  Dec 05 '17 at 18:13
  • TOAD does not "accept" any syntax. It's the database server (Oracle) that "accepts" the syntax. The SQL client you are using is irrelevant for the features the database supports –  Dec 05 '17 at 18:14
  • Toad uses Oracle. You're attempting to argue over semantics rather than just answering the question. Either answer the question or move on. – Nina Dec 05 '17 at 19:21

0 Answers0