| update [message #291137] |
Thu, 03 January 2008 02:19  |
oracle_coorgi
Messages: 188
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 #291146 is a reply to message #291140] |
Thu, 03 January 2008 02:59  |
oracle_coorgi
Messages: 188
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 #291187 is a reply to message #291137] |
Thu, 03 January 2008 05:34 |
 |
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 ?
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.
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 |
flyboy
Messages: 1903
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>
)
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; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
