CODEWITHSUNDEEP
sqlsql-serveroracle-database
Gleb
Gleb

Reputation: 1432

Oracle update using subquery

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?

Upvotes: 1

Views: 90

Answers (2)

jimmbraddock
jimmbraddock

Reputation: 887

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

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

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)

Upvotes: 2

Related Questions