Home » SQL & PL/SQL » SQL & PL/SQL » update
update [message #291137] Thu, 03 January 2008 02:19 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
update t1 t1
SET t1.col1 = SUBSTR(t2.col3,18,3)||SUBSTR(t2.col7,18,5)||t1.col1

ORA-00904: "t2"."col7": invalid identifier

who can i update table column from other table column SUBSTR
Re: update [message #291140 is a reply to message #291137] Thu, 03 January 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Several ways:
- use correlated subquery
- updatable view
- merge statement

Regards
Michel
Re: update [message #291146 is a reply to message #291140] Thu, 03 January 2008 02:59 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
cannot use merege
when co-related query used
update t1 t1
SET t1.col1 = (select SUBSTR(t2.col3,18,3)||SUBSTR(t2.col7,18,5)||t1.col1
from t2,t1)
i cannot use where caluse

ORA-01427: single-row subquery returns more than one row
Re: update [message #291147 is a reply to message #291146] Thu, 03 January 2008 03:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can/ have to use the where clause in the inner select, so that it will return one row to the outer sql.

By
Vamsi
Re: update [message #291149 is a reply to message #291147] Thu, 03 January 2008 03:15 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
if i use where caluse also i get same error
where t1.col1=t2.col2
Re: update [message #291153 is a reply to message #291149] Thu, 03 January 2008 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that you used a WHERE clause that doesn't restrict fetched record set enough. Add another condition (AND t1.some_column = t2.some_column AND ...), or - if you're lucky, SELECT DISTINCT.
Re: update [message #291158 is a reply to message #291146] Thu, 03 January 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

cannot use merege

Why? it is a good one.

Regards
Michel
Re: update [message #291187 is a reply to message #291137] Thu, 03 January 2008 05:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

update t1 t1
SET t1.col1 = (select SUBSTR(t2.col3,18,3)||SUBSTR(t2.col7,18,5)||t1.col1
from t2,t1)


Quote:

You can/ have to use the where clause in the inner select, so that it will return one row to the outer sql.


Quote:

if i use where caluse also i get same error
where t1.col1=t2.col2


ie , One different thought on how things went wrong.

Where did you put where condition ?

where t1.col1=t2.col2 

inside the innerselect joining t2 and t1 (from the inner select only ) ? . Then remove innerselect t1 , but join with main query t2.

Also try Littelfoot'ssuggesion along with that .

Plus , a where clause is highly appreciated in main query also in these type queries.

Thumbs Up
Rajuvan.


[Updated on: Thu, 03 January 2008 05:46]

Report message to a moderator

Re: update [message #291218 is a reply to message #291137] Thu, 03 January 2008 07:26 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you want something like
UPDATE t1
SET t1.col1 = (
  SELECT SUBSTR(t2.col3,18,3)||SUBSTR(t2.col7,18,5)||t1.col1
  FROM t2
  WHERE t1.<join_column> = t2.<join_column>
    <AND other_join_conditions>
)
Note that T1 is NOT included in FROM clause of the inner SELECT - it is taken from the outer query, selecting row from T2 correlated to the updated row in T1.
When T1 was included in the inner query, it returned ALL result rows (just as you would run it separately) for each outer query row; that's why the error.
Also note you update ALL rows in T1; those without representation in T2 will be set to NULL.

Another option would be using updatable view, something like
UPDATE (
  SELECT t1.col1 col1,
    SUBSTR(t2.col3,18,3)||SUBSTR(t2.col7,18,5)||t1.col1 col11
  FROM t1, t2
  WHERE t1.<join_column> = t2.<join_column>
    <AND other_join_conditions>
) t
SET t.col1 = t.col11;
Previous Topic: Difference between the following combination : CREATE-REPLACE & DROP-CREATE
Next Topic: Get the running time
Goto Forum:
  


Current Time: Fri Dec 02 23:13:20 CST 2016

Total time taken to generate the page: 0.11556 seconds