Skip to content
Fix Code Error

Update statement with inner join on Oracle

March 13, 2021 by Code Error
Posted By: Anonymous

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 – “SQL command not properly ended”

The query is:

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

Solution

That syntax isn’t valid in Oracle. You can do this:

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

Or you might be able to do this:

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

It depends if the inline view is considered updateable by Oracle
( To be updatable for the second statement depends on some rules listed
here
).

Answered By: Anonymous

Related Articles

  • How to remove MySQL completely with config and…
  • Use jQuery to scroll to the bottom of a div with…
  • SQL query return data from multiple tables
  • Julia - Understanding JuMP Gurobi outputs
  • Examples of GoF Design Patterns in Java's core libraries
  • What's the difference between eval, exec, and compile?
  • CSS Float: Floating an image to the left of the text
  • Should MySQL have its timezone set to UTC?
  • data.table vs dplyr: can one do something well the…
  • SQL JOIN and different types of JOINs
  • how to transform a JSON coming from an API into…
  • How can I fix MySQL error #1064?
  • Howto: Clean a mysql InnoDB storage engine?
  • PHP parse/syntax errors; and how to solve them
  • error: 'Can't connect to local MySQL server through…
  • What is a NullReferenceException, and how do I fix it?
  • How to filter a RecyclerView with a SearchView
  • Union of multiple Database queries with same parameters
  • SQL "select where not in subquery" returns no results
  • What are the undocumented features and limitations…
  • Are PDO prepared statements sufficient to prevent…
  • What does "Fatal error: Unexpectedly found nil while…
  • INNER JOIN vs LEFT JOIN performance in SQL Server
  • Ukkonen's suffix tree algorithm in plain English
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • For-each over an array in JavaScript
  • phpMyAdmin on MySQL 8.0
  • What is your most productive shortcut with Vim?
  • Reference - What does this regex mean?
  • If I built an app with a CSS framework, and then…
  • SQL Server JOIN missing NULL values
  • How to paste yanked text into the Vim command line
  • How does PHP 'foreach' actually work?
  • Get protocol + host name from URL
  • SQLGrammarException:error executing work ORA-01722:…
  • How to use java.net.URLConnection to fire and handle…
  • ERROR 1698 (28000): Access denied for user…
  • Identifying and solving…
  • Regex on htaccess file gives INTERNAL REDIRECT error
  • Is there a SQL function I can use to rotate a…
  • What are the new features in C++17?
  • SQL Error: ORA-00933: SQL command not properly ended
  • What does a "Cannot find symbol" or "Cannot resolve…
  • How can I initialize a MySQL database with schema in…
  • From inside of a Docker container, how do I connect…
  • Addition of two queries - SQL/HANA
  • Pandas Merging 101
  • Usage of __slots__?
  • Check synchronously if file/directory exists in Node.js
  • How to set root password to null
  • java.sql.SQLException: - ORA-01000: maximum open…
  • How do I count unique visitors to my site?
  • Chrome / Safari not filling 100% height of flex parent
  • How to generate a random string of a fixed length in Go?
  • Can a table row expand and close?
  • What is an optional value in Swift?
  • Understanding PrimeFaces process/update and JSF…
  • Why doesn't the height of a container element…
  • Difference between variable declaration syntaxes in…
  • Rewrite left outer join involving multiple tables…
  • What does this symbol mean in JavaScript?
  • java.sql.SQLException: Access denied for user…
  • PHP mysql insert date format
  • SQL Server: Query fast, but slow from procedure
  • In Oracle DB how to avoid decompiling of objects…
  • MySQL ERROR 1045 (28000): Access denied for user…
  • MySQL server has gone away - in exactly 60 seconds
  • Can not connect to amazon RDS from spring boot container app
  • MySQL fails on: mysql "ERROR 1524 (HY000): Plugin…
  • NOT IN vs NOT EXISTS
  • commandButton/commandLink/ajax action/listener…
  • Why do I have to "git push --set-upstream origin "?
  • Java string to date conversion
  • Oracle (ORA-02270) : no matching unique or primary…
  • What does "Could not find or load main class" mean?
  • Make: "nothing to be done for target" when invoking…
  • Difference between left join and right join in SQL Server
  • How to change the MySQL root account password on CentOS7?
  • Check if value is in another table and add columns…
  • SQL left join vs multiple tables on FROM line?
  • Why does C++ code for testing the Collatz conjecture…
  • What's the difference between Instant and LocalDateTime?
  • What is an IndexOutOfRangeException /…
  • mysql.h file can't be found
  • Programmatically generate url from path and params
  • OR condition in update impacting performance
  • How do Mockito matchers work?
  • Cannot write if or select above begin on a procedure
  • fail to start mysql on CentOS7
  • Vue&TypeScript: how to avoid error TS2345 when…
  • How to find out client ID of component for ajax…
  • Using IS NULL or IS NOT NULL on join conditions -…
  • How to find patterns across multiple lines using grep?
  • UTL_FILE.FOPEN() procedure not accepting path for directory?
  • INNER JOIN ON vs WHERE clause
  • Individual click handlers in v-for loop
  • Oracle TNS names not showing when adding new…
  • MySql Inner Join with WHERE clause
  • How to convert java.util.Date to java.sql.Date?
  • update one table with data from another

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Get property value from string using reflection

Next Post:

Run jar file in command prompt

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error