Take the 2-minute tour ×

I'm trying to support multiple databases for an app that I'm writing. The app mostly uses Hibernate, but it's incredibly inefficient to iterate over millions of rows and process them individually when a DML statement can process them in a fraction of the time. Therefore, for certain operations, I need to work out the SQL. I'm more of a MySQL man, but I have the app working with SQL Server and MySQL so far. There's one operation that has stumped me, and I just can't seem to figure out how to construct some of the update queries for Oracle. I'm aware that this is probably a newbie question as far as Oracle goes, but I must have missed the obvious answers when I've been looking....

Here's how I write this type of query for MySQL:

update table1 t1, table2 t2 set t1.colA = t2.colA where t1.colB = t2.colB and t1.colC = t2.colC

MySQL has a nice construct where you can just specify all tables before the 'set' statement with aliases, which greatly simplifies the rest of the statement. In SQL Server, I use update ... join to do the same thing.

In Oracle, I've tried using the 'update table1 set colA = (select ....) where exists (select ....) syntax, but this doesn't work - it returns a 'subquery returns more than one row' error. I've also tried to use the merge ... using ... on syntax, but that errors with 'unable to get a stable set of rows from the source tables'.

To further explain what I'm trying to achieve, I have a number of queries to perform, some of them using 2 tables, some using 3 tables. The most complex needs to do this:

update tableA.colB with the value in tableC.colC where tableA.colA = tableB.colA and tableB.colB = tableC.colB for all matched rows. In data terms, this looks like this (Before and after):


Table A
colA     colB
1        NULL
2        NULL
3        NULL
4        NULL

Table B
colA     colB
1        A
2        A
3        B
4        B

Table C
colB     colC
A        15
B        20


Table A
colA     colB
1        15
2        15
3        20
4        20

I hope this is clear enough. Can anyone explain how to write this kind of DML query for Oracle? For bonus points, will it be the same for PostgreSQL? :)

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

You can use distinct to ignore multiple copies of the same value:

update  TableA
set     ColB = 
        select  distinct ColC
        from    TableC C
        join    TableB B
        on      C.ColB = B.ColB
        where   B.ColA = TableA.ColA

This still gives an error if more then one suitable value is found.

share|improve this answer
Yeah, that's not what I'm after, because the subquery will return more than one row. I don't want to have to run an update for each distinct TableC.colC value. In Oracle and SQL Server, I can achieve this in a single DML operation. I suppose I'm saying 'update TableA set colB = TableC.colB for every case where tableA.colA = tableB.colA and tableB.colB = tableC.colA'. –  Mick Sear Sep 30 '10 at 10:57
@Mick Sear: The subquery runs for each row in TableA. If it returns more than one value, which one should it choose to update ColB? –  Andomar Sep 30 '10 at 11:01
OK, I see it now, after running the subselect independently. Many thanks –  Mick Sear Sep 30 '10 at 11:32
add comment

Try the following:

UPDATE table_a a
  SET col_b = (SELECT col_c
                 FROM table_b b,
                      table_c c
                 WHERE b.col_a = a.col_a AND
                       c.col_b = b.col_b);

I don't happen to have Postgresql on this machine so can't comment on that.

Share and enjoy.

share|improve this answer
Yeah, I tried that along the way actually. The issue is clearly with my data. Solved with Andomar's help. –  Mick Sear Sep 30 '10 at 11:38
add comment


    SQL> select * from A
      2  /

          COLA       COLC

    SQL> select * from B
      2  /

          COLA C
    ---------- -
             1 A
             2 A
             3 B
             4 B

    SQL> select * from C
      2  /

    C       COLC
    - ----------
    A         15
    B         20


The query

    SQL> update A
      2  set colc = ( select c.colc
      3               from c
      4                      join b on ( c.colB = b.colB )
      5               where
      6                  b.colA = A.colA )
      7  where exists
      8      ( select null
      9               from c
     10                      join b on ( c.colB = b.colB )
     11               where
     12                  b.colA = A.colA )
     13  /

    4 rows updated.



    SQL> select * from A
      2  /

          COLA       COLC
             1         15
             2         15
             3         20
             4         20


Obviously you have simplified something in your test case so it is not representative of your actual situation. The key thing is, my query works because the sub-query returns a single row for each value of A.colA. You need to re-visit your data and establish the necessary criteria. This is a data/business logic problem, not a syntax problem.

share|improve this answer
Correct. Thanks for your reply. It's now solved by using 'distinct' in the subselect as per andomar's response, pending further testing –  Mick Sear Sep 30 '10 at 11:37
add comment

Your Answer


