Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE question => updating with information from other table (Oracle 10i)
UPDATE question => updating with information from other table [message #434435] Wed, 09 December 2009 05:02 Go to next message
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...
Re: UPDATE question => updating with information from other table [message #434442 is a reply to message #434435] Wed, 09 December 2009 05:16 Go to previous messageGo to next message
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;
Re: UPDATE question => updating with information from other table [message #434443 is a reply to message #434435] Wed, 09 December 2009 05:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Merge
Re: UPDATE question => updating with information from other table [message #434473 is a reply to message #434442] Wed, 09 December 2009 06:11 Go to previous messageGo to next message
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?
Re: UPDATE question => updating with information from other table [message #434478 is a reply to message #434473] Wed, 09 December 2009 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So add a where clause to your update, like "where exists ..."

Regards
Michel
Re: UPDATE question => updating with information from other table [message #434480 is a reply to message #434435] Wed, 09 December 2009 06:19 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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.
Re: UPDATE question => updating with information from other table [message #434485 is a reply to message #434473] Wed, 09 December 2009 06:27 Go to previous messageGo to next message
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)
Re: UPDATE question => updating with information from other table [message #434491 is a reply to message #434485] Wed, 09 December 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: UPDATE question => updating with information from other table [message #434510 is a reply to message #434478] Wed, 09 December 2009 08:20 Go to previous messageGo to next message
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 (??)
Re: UPDATE question => updating with information from other table [message #434511 is a reply to message #434510] Wed, 09 December 2009 08:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: UPDATE question => updating with information from other table [message #434524 is a reply to message #434516] Wed, 09 December 2009 08:59 Go to previous messageGo to next message
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
Re: UPDATE question => updating with information from other table [message #434531 is a reply to message #434516] Wed, 09 December 2009 10:09 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Fri Dec 02 14:32:56 CST 2016

Total time taken to generate the page: 0.15340 seconds