Oracle: update a column in table a from values in other tables

Question

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):

Before:

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

After:

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? :)

Answer 1

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.

Answer 2

before

    SQL> select * from A
      2  /

          COLA       COLC
    ---------- ----------
             1
             2
             3
             4

    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

    SQL>

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>

After

    SQL> select * from A
      2  /

          COLA       COLC
    ---------- ----------
             1         15
             2         15
             3         20
             4         20

    SQL>

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.

Answer 3

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.