OraFAQ Forum: SQL & PL/SQL » UPDATE question => updating with information from other table

Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE question => updating with information from other table (Oracle 10i)

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

UPDATE question => updating with information from other table [message #434435]

Wed, 09 December 2009 05:02

s-t-e-i-n
Messages: 4
Registered: December 2009

Junior Member

I used the search function, but I´m not sure what to ask.

Here is my task:

I have 2 Tables:

T1 (NR, CURRENTCONTENT)
T2 (NR, NEWCONTENT)

I want to do something like this: (obviously no valid statement)
UPDATE T1
SET T1.CURRENTCONTENT = T2.NEWCONTENT

The tables can by joined on T1.NR = T2.NR

I have no Idea how to join within an UPDATE statement...

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434442 is a reply to message #434435]

Wed, 09 December 2009 05:16

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Traditional Approach

UPDATE t1
set    currentcontent = (SELECT newcontent
                         FROM   t2
                         where t1.nr = t2.nr)

Merge

merge into t1
using (select nr
             ,newcontent
       from   t2) t2
on t1.nr = t2.nr
when matched then update set t1.currentcontent = t2.newcontent;

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434443 is a reply to message #434435]

Wed, 09 December 2009 05:16

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Merge

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434473 is a reply to message #434442]

Wed, 09 December 2009 06:11

s-t-e-i-n
Messages: 4
Registered: December 2009

Junior Member

JRowbottom wrote on Wed, 09 December 2009 05:16

Traditional Approach

UPDATE t1
set    currentcontent = (SELECT newcontent
                         FROM   t2
                         where t1.nr = t2.nr)

Thanks for the hint. But this will only work if there is a row in t2 for every t1.NR. Else you update the CURRENTVALUE WITH NULL, whis is ugly when it is (part of) a key.

When t1 has 100 rows, and t2 has 20, and I want to update CURRENTCONTENT with the 20 matching NEWCONTENT, what do I do?

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434478 is a reply to message #434473]

Wed, 09 December 2009 06:19

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

Senior Member
Account Moderator

So add a where clause to your update, like "where exists ..."

Regards
Michel

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434480 is a reply to message #434435]

Wed, 09 December 2009 06:19

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

Senior Member

Add a where cluase to the update to check if a matching row exists in the other table, or use the merge solution.

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434485 is a reply to message #434473]

Wed, 09 December 2009 06:27

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Or you take advantage of the fact that it returns null and do this:

UPDATE t1
set    currentcontent = NVL(SELECT newcontent
                            FROM   t2
                            where t1.nr = t2.nr),currentcontent)

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434491 is a reply to message #434485]

Wed, 09 December 2009 06:42

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

Senior Member
Account Moderator

I'd not recommend a method that will update each and every rows when only some of them must be updated.

Regards
Michel

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434510 is a reply to message #434478]

Wed, 09 December 2009 08:20

s-t-e-i-n
Messages: 4
Registered: December 2009

Junior Member

Michel Cadot wrote on Wed, 09 December 2009 06:19

So add a where clause to your update, like "where exists ..."

Regards
Michel

Where would I place the WHERE EXISTS ? And what would be in the brackets? I would have to use "WHERE EXISTS(NEWCONTENT)", but this won´t work because there is no way to link to the NEWCONTENT and no alias, or am I missing something?

T1: 100 rows
T2: 20 rows

UPDATE T1
SET CURRENTCONTENT = (SELECT NEWCONTENT
FROM T2
WHERE T1.NR = T2.NR)
WHERE EXISTS (??)

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434511 is a reply to message #434510]

Wed, 09 December 2009 08:28

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

UPDATE T1
SET CURRENTCONTENT = (SELECT NEWCONTENT 
FROM T2
WHERE T1.NR = T2.NR)
WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.NR = T1.NR)

This requires a second access to the table T2, which is why the 'update all rows' or MERGE approaches can be preferable.

[add code tags]

[Updated on: Wed, 09 December 2009 08:31]

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434516 is a reply to message #434511]

Wed, 09 December 2009 08:40

s-t-e-i-n
Messages: 4
Registered: December 2009

Junior Member

JRowbottom wrote on Wed, 09 December 2009 08:28

WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.NR = T1.NR)

What does "SELECT 1" stand for? What is the 1? An alias or a field, and for which?

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434524 is a reply to message #434516]

Wed, 09 December 2009 08:59

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

It's the lowest non-zero natural number, and the identity value for multiplication and division operations

An EXISTS subquery just checks to see if there are any rows to return - by not selecting a row from a table you allow the CBO to potentially pick more index only access paths and run the subquery quicker

Report message to a moderator

Re: UPDATE question => updating with information from other table [message #434531 is a reply to message #434516]

Wed, 09 December 2009 10:09

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

Senior Member
Account Moderator

As what is in the select part is meaningless I prefer to use "SELECT NULL" to show that no value is required/returned.

Regards
Michel

[Updated on: Wed, 09 December 2009 10:10]

Report message to a moderator

Previous Topic:	Needed a query for the situation like
Next Topic:	table row lock question (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:42:20 CST 2024