Home » SQL & PL/SQL » SQL & PL/SQL » Update Table Based On Another Table (oracle 11g, windows server)
Update Table Based On Another Table [message #657666] Fri, 18 November 2016 05:16 Go to next message
morissa
Messages: 4
Registered: November 2016
Junior Member
Hello guys thanks for reading and helping, i need to update a table based on another table
but there's a lil bit diffrent structure, i attach image for more description.

[img]https://postimg.org/image/b6tt9fnez/]https://s17.postimg.org/b6tt9fnez/plsql.png

any suggest will be appreciate, thank you very much
Re: Update Table Based On Another Table [message #657667 is a reply to message #657666] Fri, 18 November 2016 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can't see the image due to proxy restrictions. Consider attaching it to your message directly (not using a link).

Anyway: can you join these two tables? If so, you can (probably) do what you asked. Did you try it? What kind of a query did you manage to write so far?
Re: Update Table Based On Another Table [message #657668 is a reply to message #657667] Fri, 18 November 2016 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 13263
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can see the image and it's too small to be readable.
Post the table descriptions and contents as plain text, describe with words the relationship between them.
Re: Update Table Based On Another Table [message #657669 is a reply to message #657667] Fri, 18 November 2016 05:47 Go to previous messageGo to next message
morissa
Messages: 4
Registered: November 2016
Junior Member
i can join this table but this diffrent strtucture give me headeche, im sorry i cant speak english, so i upload this image for more description
i will attach the image directly

my query so far

update TABLE1 set TABLE1.STATUS = TABLE2.STATUS
from
TABLE1
inner join TABLE2 on TABLE1.NAME = TABLE2.NAME AND TABLE1.YEAR = TABLE2.YEAR ;

but this is wrong because the diffrent structure..

im sorry but how i can attach image? i cant see the option too attach image?

here i attach again i dont know if this directly or not?

/forum/fa/13327/0/



Thank you for your response!

  • Attachment: plsql.png
    (Size: 14.82KB, Downloaded 376 times)

[Updated on: Fri, 18 November 2016 05:49]

Report message to a moderator

Re: Update Table Based On Another Table [message #657671 is a reply to message #657669] Fri, 18 November 2016 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65830
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use MERGE.

Re: Update Table Based On Another Table [message #657672 is a reply to message #657671] Fri, 18 November 2016 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 13263
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't do joins on updates like that in oracle, so a sub-query, or merge, is the way to go.
To do the specific join with the data structure instr is the function to use:
UPDATE table1 t1 
SET status = (SELECT status 
              FROM table2 t2
              WHERE t2.name = t1.name
              AND instr(t2.year, t1.year) > 0
             )
WHERE EXISTS (SELECT null
              FROM table2 t2
              WHERE t2.name = t1.name
              AND instr(t2.year, t1.year) > 0
             )
Re: Update Table Based On Another Table [message #657673 is a reply to message #657669] Fri, 18 November 2016 06:40 Go to previous messageGo to next message
EdStevens
Messages: 975
Registered: September 2013
Senior Member
Part of your problem is your data design. What is is the purpose of table1 vs. table2? Why does table2 have multiple data elements in a single column? That, in and of itself, is a FAIL for Relational Database Design 101. Design your tables to Third Normal form and your problem goes away.
Re: Update Table Based On Another Table [message #657674 is a reply to message #657673] Fri, 18 November 2016 06:54 Go to previous messageGo to next message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
EdStevens wrote on Fri, 18 November 2016 04:40
Part of your problem is your data design. What is is the purpose of table1 vs. table2? Why does table2 have multiple data elements in a single column? That, in and of itself, is a FAIL for Relational Database Design 101. Design your tables to Third Normal form and your problem goes away.
+1
Re: Update Table Based On Another Table [message #657675 is a reply to message #657674] Fri, 18 November 2016 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, maybe he is converting it to 3rd normal form right now ...
Re: Update Table Based On Another Table [message #657683 is a reply to message #657672] Fri, 18 November 2016 09:15 Go to previous messageGo to next message
morissa
Messages: 4
Registered: November 2016
Junior Member
cookiemonster wrote on Fri, 18 November 2016 06:31
You can't do joins on updates like that in oracle, so a sub-query, or merge, is the way to go.
To do the specific join with the data structure instr is the function to use:
UPDATE table1 t1 
SET status = (SELECT status 
              FROM table2 t2
              WHERE t2.name = t1.name
              AND instr(t2.year, t1.year) > 0
             )
WHERE EXISTS (SELECT null
              FROM table2 t2
              WHERE t2.name = t1.name
              AND instr(t2.year, t1.year) > 0
             )
thank you very much for your advice i'll try it tomorow, i already at my home now..


Quote:
Part of your problem is your data design. What is is the purpose of table1 vs. table2? Why does table2 have multiple data elements in a single column? That, in and of itself, is a FAIL for Relational Database Design 101. Design your tables to Third Normal form and your problem goes away.
the TABLE1 is my table on my database, and TABLE2 is a excel file which is used for updating data, i told my partner to follow the rule to make excel file with same field as TABLE1... and he came with this... Sad


thanks again for all your response guys!!



Re: Update Table Based On Another Table [message #657684 is a reply to message #657683] Fri, 18 November 2016 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Update Table Based On Another Table [message #657686 is a reply to message #657683] Fri, 18 November 2016 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65830
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 18 November 2016 13:18

Use MERGE.
Re: Update Table Based On Another Table [message #657763 is a reply to message #657686] Tue, 22 November 2016 02:42 Go to previous messageGo to next message
morissa
Messages: 4
Registered: November 2016
Junior Member
Trying

MERGE INTO TABLE1 X USING TABLE2 Y
ON (
X.NOP = Y.NOP 
AND X.YEAR LIKE '%'||Y.YEAR||'%'
)
WHEN MATCHED THEN
  UPDATE SET X.STATUS = Y.STATUS
 


Field X.STATUS doesnt updated, still no luck Confused
Re: Update Table Based On Another Table [message #657766 is a reply to message #657763] Tue, 22 November 2016 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65830
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is because you didn't post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Update Table Based On Another Table [message #657768 is a reply to message #657766] Tue, 22 November 2016 03:38 Go to previous message
cookiemonster
Messages: 13263
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got the like the wrong way round.
2011 can't be like %2010,2011%
Previous Topic: Show all months between 2 months in a group by query
Next Topic: electronic data sharding
Goto Forum:
  


Current Time: Mon Oct 15 05:24:38 CDT 2018