|
|
|
|
|
|
|
|
|
|
| Re: Update Table Based On Another Table [message #657672 is a reply to message #657671] |
Fri, 18 November 2016 06:31   |
cookiemonster
Messages: 13919
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 #657683 is a reply to message #657672] |
Fri, 18 November 2016 09:15 |
 |
morissa
Messages: 4
Registered: November 2016
|
Junior Member |
|
|
cookiemonster wrote on Fri, 18 November 2016 06:31You 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... 
thanks again for all your response guys!!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
