Oracle update using subquery

Question

I need to convert my update Ms SQL Server query for Oracle

I have two tables:

Table1(id int, SaveId int, ChangeId int)
Table2(id int, smSaved int, boldId int)

The query is:

UPDATE
  Table1
Set 
  SaveId=tbl.smSaved
FROM
  Table1 tbl1
join Table2 tbl on tbl1.ChangeId=tbl.boldId

In MsSql everything works fine, but when I'm trying to run it in Oracle thats doesn't works. I can't figure out whats wrong with it.

Can anybody explain me what I need to change?

The MS SQL Server UPDATE with FROM is product specific, and not included in the ANSI/ISO SQL standard. — jarlh
– jarlh, Commented Apr 17, 2015 at 7:29

Joe Taras · Accepted Answer · 2015-04-17 07:26:21Z

2

Try this:

UPDATE table1 SET SaveId = 
    (SELECT tbl.saved FROM table2 tbl
    WHERE tbl.boldId = table1.ChangeId)
WHERE EXISTS(
    SELECT tbl.saved FROM table2 tbl
    WHERE tbl.boldId = table1.ChangeId
)

The first part of query update SaveId with value of tbl.saved (I suppose you have only one row of table2 linked with table1.

The second part (exists) guarantee you have a correspond between two table (if you add exists you put NULL where not exists row in table2)

answered Apr 17, 2015 at 7:26

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

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

Comments

jimmbraddock · Accepted Answer · 2015-04-17 08:34:27Z

1

You are may to use update with subquery same MS SQL. In Oracle its look like:

UPDATE (SELECT t1.savedId t1_saved
         , t2.smsaved t2_saved
      FROM table1 t1
         , table2 t2
     WHERE t1.changeid = t2.boldid)
SET t1_saved = t2_saved

answered Apr 17, 2015 at 8:34

jimmbraddock

9073 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

Oracle update using subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related