Update query runs in SQL Server but not in Oracle

Question

I need this update query to run on both SQL Server and Oracle. Our Oracle version is 10.2 if that matters. When I run the query in Oracle I get "ERROR ORA-00933: SQL command not properly ended". What do I need to do to get this to run in Oracle?

UPDATE dbo.tableUpdate
SET fieldA = tt.fieldB
FROM dbo.tableTranslate tt
WHERE
    tt.fieldC = dbo.tableUpdate.fieldC
    AND
    tt.fieldD = dbo.tableUpdate.fieldA
    AND
    1 = (
        SELECT COUNT(tblTrans.fieldD) 
        FROM dbo.tableTranslate tblTrans
        WHERE 
            tblTrans.fieldC = dbo.tableUpdate.fieldC 
            AND 
            tblTrans.fieldD = dbo.tableUpdate.fieldA
)

Tony Andrews · Accepted Answer · 2010-11-04 14:21:32Z

6

The UPDATE...FROM syntax is not valid for Oracle. You will need to use a subquery, like this:

UPDATE dbo.tableUpdate t
SET t.fieldA = (SELECT tt.fieldB
                FROM dbo.tableTranslate tt
                WHERE tt.fieldC = t.fieldC
                AND tt.fieldD = t.fieldA
               )
WHERE 1 = (
        SELECT COUNT(tblTrans.fieldD) 
        FROM dbo.tableTranslate tblTrans
        WHERE tblTrans.fieldC = t.fieldC 
        AND tblTrans.fieldD = t.fieldA
        )

answered Nov 4, 2010 at 14:21

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajesh Chamarthi · Accepted Answer · 2010-11-04 14:22:10Z

0

The syntax for co-related sub query is a little different in Oracle.

UPDATE dbo.tableUpdate
SET fieldA = (select tt.fieldB
FROM dbo.tableTranslate tt
WHERE
    tt.fieldC = dbo.tableUpdate.fieldC
    AND
    tt.fieldD = dbo.tableUpdate.fieldA)
    AND
    1 = (
        SELECT COUNT(tblTrans.fieldD) 
        FROM dbo.tableTranslate tblTrans
        WHERE 
tblTrans.fieldC = dbo.tableUpdate.fieldC 
AND 
tblTrans.fieldD = dbo.tableUpdate.fieldA)

answered Nov 4, 2010 at 14:22

Rajesh Chamarthi

18.9k4 gold badges43 silver badges67 bronze badges

Comments

Pablo Santa Cruz · Accepted Answer · 2010-11-04 14:22:43Z

0

You will need to rewrite completely your query for Oracle. Some stuff that won't work in Oracle 10.2:

from clause in update sentence (you will need to write a subselect for this)
dbo schema, unless you actually have a user named dbo in oracle owning the table you are trying to update

answered Nov 4, 2010 at 14:22

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Collectives™ on Stack Overflow

Update query runs in SQL Server but not in Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related