Oracle 10g/11g - how to populate column in t1 with data from t2 with 'where' clause

Hello, everyone,

I originally did not think that this would be difficult. But, either I’m over-thinking this (quite possible) or it’s more complex than I anticipated. I’ll try to explain without being overbearing.

I have table1 that contained three columns: USER_ID (varchar), QID (varchar), DATE_ENTERED (date)

I have table 2 that contains several columns, but the only ones that apply, here, are ORG_ID (varchar), USER_ID (varchar), and ROLE (varchar)

I have added ORG_ID to table 1.

I need to populate table 1 ORG_ID with corresponding ORG_ID in table 2 based upon matching USER_ID in both tables AND table 2 ROLE = ‘leader’.

Here is what I have, so far. (I think it’s working in development db, but it doesn’t have current production db data.)

UPDATE table1 t1
SET (t1.ORG_ID) = (
     FROM table2 t2
     WHERE t2.USER_ID = t1.USER_ID
          AND lower(TRIM(BOTH ' ' FROM t2.ROLE)) = 'leader'
          AND ROWNUM = 1

Is this correct? Is there a better way to do this?

Thank you,

UPDATE: I went ahead and gave this to one of our DBAs to run in production. This works (as far as I can tell) just fine in development; but as soon as she ran it in production, it’s throwing an error.

Trying to get precisely what the error is.

haven’t used oracle in over a decade, don’t know what the error might be

but from your last post, strong guess is dev and prod aren’t running the same version

No, as it turns out one is 10g, the other 11g.

I still don’t have an exact error message, but I did see in the command line “SP2-0158” which (as I understand it) is something wrong with the SET command. Could an alias (t1) be the culprit?


no idea

time to dig out those lovely railway-yard diagrams defining oracle’s peculiar sql syntax

I was just informed that both are actually 11g - the update happened when I wasn’t looking. :slight_smile:

UPDATE: A fellow developer fixed it. Apparently, Oracle doesn’t like double-spaced queries. So the fix was to change this:

UPDATE table1 t1


    SELECT blah blah blah

    FROM blah blah


… to this:

UPDATE table1 t1
SET ORG_ID = (SELECT blah blah blah
    FROM blah blah)

GTFO !!!

I know!!! Messed up, ain’t it? WTF? But it updated over 500 records after removing the blank lines. Apparently, Oracle sees the extra lines as end of a function. Without the semi-colon.