Update Error

[JPCogAdmin]

Good morning,

I'm trying to update one field in a specific user schema by using another user's schema fields and I get an error:

=> SQL Error: ORA-00904: invalid identifier

The SQL code I'm running is as follow:
===============================================
update user1.table1 P set (phone) = (select user2.table2.phone from user2.table2 af
where P.ID = af.ID AND P.addr=af.addr)
===============================================
As you can see my goal is to update table1's phone number
with the phone number from table2 which is owned by a different schema. Any clarification is greatly appreciated.
-J

 

[SantaMufasa]

JP,

There are a couple of issues with your code:

1) Once you create an alias for a table in a SQL statement, if you plan on qualifying that same table in the SQL statement, you must use that alias. For example, you gave the table "user2.table2" the alias "af", yet the table qualification you gave in the code was "user2.table2.phone"; you should have said "af.phone":

(original code)
update user1.table1 P set (phone) = (select user2.table2.phone from user2.table2 af
where P.ID = af.ID AND P.addr=af.addr);

ERROR at line 1:
ORA-00904: "USER2"."TABLE2"."PHONE": invalid identifier

(corrected code)
update user1.table1 P set (phone) = (select af.phone from user2.table2 af
where P.ID = af.ID AND P.addr=af.addr);

4 rows updated.

Here are the original contents of the two tables that I created to illustrate your situation:

SQL> select * from table1;

        ID PHONE           ADDR
---------- --------------- --------
         1                 abc
         2
         3                 xyz
         4

SQL> select * from user2.table2;

        ID PHONE           ADDR
---------- --------------- --------
         1 801-555-1212    abc
         3 801-555-5555    xyz

2) Notice in my corrected UPDATE statement, above, Oracle updated four rows in table1, while it needed to UPDATE only two rows...rows 2 and 4 updated with NULL values...a waste of time. Therefore you can eliminate that waste with the following code adjustment:

update user1.table1 P
       set (phone) = (select af.phone
                        from user2.table2 af
                       where P.ID = af.ID AND P.addr=af.addr)
 where exists (select NULL
                 from user2.table2 af
                where P.ID = af.ID AND P.addr=af.addr);

2 rows updated.

select * from table1;

   ID PHONE           ADDR
----- --------------- -------
    1 801-555-1212    abc
    2
    3 801-555-5555    xyz
    4

Let us know if this gives you the insight you needed for your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

Mufasa,

Thanks for your reply. Have actually ran the code before you posted it here and I got an error message that says the following:

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

I don't know what to do next.
;-(

-JP

 

[SantaMufasa]

That error indicates that your user2.table2 has multiple rows that match user1.table1.id and user1.table1.addr. Is it erroneous/unexpected that there is a one-to-many relationship from table1 to table2? Regardless...there is such, whether you want it or not.

If it doesn't matter to you which table2 match to table1 you use, you can get past that error with this code adjustment:

update user1.table1 P
       set (phone) = (select [b]max([/b]af.phone)
                        from user2.table2 af
                       where P.ID = af.ID AND P.addr=af.addr)
 where exists (select NULL
                 from user2.table2 af
                where P.ID = af.ID AND P.addr=af.addr);

In the above code adjustment, only one row can result from a "MAX()" function.

Let us know if this resolves your problem.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

Thanks Mufasa,

I'm sure that code will run. Now I must ask here if this is what they had in mind when they designed the tables.
;-)
Thank you so much for your help. I will keep you posted if this where I'm headed. Thank you so much for your insight.
;-)

-JP

 

[JPCogAdmin]

Mufasa,

One quesiton: Can you explain a bit about distinct?
If I have multiple columns that need to be distinct and I need to count them how would I do that?

Could it be like this: count(distinct(field1,field2,etc)) from whereever
where contition_is_met;

Can you explain that to me if you can. Thanks.

-JP

 

[SantaMufasa]

You probably want to say something like:

SELECT count(distinct field1) name1
      ,count(distinct field2) name2
      ,count(distinct field3) name3
...

Let us know if this answers your question

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

thanks Mufasa,
;-)
So one by one is the key?
the distinct can't be done in one shot?

-JP

 

[SantaMufasa]

JP, the "COUNT()" aggregate function accommodates a single expression only. The option that I posted is the closest to "oneness" that you can get.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

Thanks Mufasa,

Thank you.
;-)

-JP