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

Databases
1

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) = (
     SELECT t2.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,

2

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.

3

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

4

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?

Thanks,

5

no idea

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

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm

6

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

7

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


UPDATE table1 t1

SET ORG_ID = (

    SELECT blah blah blah

    FROM blah blah

)

… to this:


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

GTFO !!!

9

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.