OraFAQ Forum: SQL & PL/SQL » Update table from another table

Home » SQL & PL/SQL » SQL & PL/SQL » Update table from another table (oracle 9.2 solaris 10 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update table from another table [message #495916]

Wed, 23 February 2011 04:40

koff10
Messages: 58
Registered: December 2006
Location: france

Member

Hi ALL,

I want to update table1 from table2
I wrote statement like this:

UPDATE
TABLE1 t
JOIN TABLE2 tmp
ON t.username = tmp.username
SET
t.password = tmp.password;

It works fine in my MYSQL DATABASE.
BUT in ORACLE 9.2 DATABASE, I got Error like this:

JOIN TABLE2 tmp
*
ERROR at line 3:
ORA-00971: missing SET keyword

I tried inner join or natural join => same error.
May be I missed somethings ?

Thanks for help.
koff10

Report message to a moderator

Re: Update table from another table [message #495919 is a reply to message #495916]

Wed, 23 February 2011 04:43

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You need to correlate it.

UPDATE
TABLE1 t
set t.password = (select tmp.password
from table2 tmp
where
t.username = tmp.username);

Report message to a moderator

Re: Update table from another table [message #495920 is a reply to message #495916]

Wed, 23 February 2011 04:46

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Either:

UPDATE TABLE1 t 
SET PASSWORD = (SELECT PASSWORD
                FROM TABLE2
                WHERE username = t.username)
WHERE EXISTS (SELECT NULL FROM 
              FROM TABLE2
              WHERE username = t.username);

or, assuming username has a pk or unique index on it in both tables:

UPDATE (SELECT t.password old_pass, tmp.password new_pass
        FROM TABLE1 t
        JOIN TABLE2 tmp
        ON t.username = tmp.username)
SET old_pass = new_pass;

Report message to a moderator

Re: Update table from another table [message #495925 is a reply to message #495920]

Wed, 23 February 2011 05:10

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or:

MERGE INTO table1 t USING table2 tmp ON (t.username = tmp.username)
WHEN MATCHED THEN UPDATE SET password = tmp.password
WHEN NOT MATCHED THEN INSERT (username, password) VALUES (tmp.username, tmp.password)
/

Regards
Michel

Report message to a moderator

Re: Update table from another table [message #496093 is a reply to message #495920]

Thu, 24 February 2011 07:21

koff10
Messages: 58
Registered: December 2006
Location: france

Member

[quote title=cookiemonster wrote on Wed, 23 February 2011 11:46]Either:

UPDATE TABLE1 t 
SET PASSWORD = (SELECT PASSWORD
                FROM TABLE2
                WHERE username = t.username)
WHERE EXISTS (SELECT NULL FROM 
              FROM TABLE2
              WHERE username = t.username);

Thanks All for your helps.

But for this Scripts there is sitill error:

ORA-01427: single-row subquery returns more than one row

Report message to a moderator

Re: Update table from another table [message #496094 is a reply to message #496093]

Thu, 24 February 2011 07:24

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then there are multiple entries in table2 for a given username.
In which case which row do you want it to use?

Report message to a moderator

Re: Update table from another table [message #496095 is a reply to message #496093]

Thu, 24 February 2011 07:29

flyboy
Messages: 1903
Registered: November 2006

Senior Member

This error indicates problem in your data. There are multiple rows with the same USERNAME column value in TABLE2 table. However, column TABLE1.PASSWORD may be updated only with one value.
The question on you is: which of them shall be taken?
After you determine it, enhance the UPDATE statement with additional condition covering it.

Report message to a moderator

Previous Topic:	Help in sql query
Next Topic:	Query on bulk collect

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 22:16:07 CDT 2025