Home » SQL & PL/SQL » SQL & PL/SQL » join condition update
join condition update [message #210950] Sun, 24 December 2006 01:34 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have two tables like this



SQL> SELECT * FROM G_APP_USER;

       ID NAME
--------- --------------------
        1 NAME1
        2 NAME2
        3 NAME3

SQL> SELECT * FROM mem;

       ID   USER_ID
--------- ---------
       10         1
       20         1
       30         2
       40         2
       50         3
       60         3

6 rows selected.




the common field between these 2 tables is id from 1st table
and user_id from 2nd table

my input is id from mem, i should be able to update
name field from 1st table..


i tried this

UPDATE G_APP_USER SET NAME='GAUTAM' WHERE
g_app_user.ID=(SELECT USER_ID FROM MEM WHERE MEM.USER_ID=G_APP_USER.ID AND G_APP_USER=1)



i am getting this error

ORA-00904: invalid column name
Re: join condition update [message #210959 is a reply to message #210950] Sun, 24 December 2006 04:59 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Error you got is because of this part of your code:
AND G_APP_USER = 1

There is no 'g_app_user' column in any of those tables. To avoid it, you should have had
AND G_APP_USER.ID = 1

But then you'd probably run into TOO-MANY-ROWS error because of "=" sign - it should be "IN" instead:
WHERE id IN (SELECT ...)

However, I didn't quite understand why do you have to look at the 'mem' table? The same would be done using a simple
UPDATE g_app_user SET name = 'GAUTAM' WHERE id = 1;
Previous Topic: to get table's column from user_objects
Next Topic: autogen without sequence
Goto Forum:
  


Current Time: Mon Dec 05 21:23:09 CST 2016

Total time taken to generate the page: 0.07453 seconds