Home » RDBMS Server » Server Administration » updating fields?
| updating fields? [message #374604] |
Wed, 20 June 2001 16:21  |
Jen
Messages: 23
Registered: June 2001
|
Junior Member |
|
|
I have 2 tables, one with 409 rows and one with 426 rows.
I'm trying to update all rows in the table with 426 rows based on the parent_name of the table with 426 rows matching the parent_name of the table with 409 rows.
If I'm not incorrect, a successful update would update 409 rows or less, depending on parent_name, since parent_name is a unique identifier.
but my update statement updates all 426 rows!
I figure I must be doing something wrong here, could someone look at this?
update tcsdw_par_cust_wc_st1 k
set (k.parent_name, k.MTPVF, k.EXIST_NEW, k.PAR_WORK_CTR,
k.MKT_SEG, k.INDSEGA, k.INDSEGB, k.DRIVER, k.WHse_LD_DATE) =
(select l.parent_name, l.MTPVF, l.EXIST_NEW, l.PAR_WORK_CTR,
l.MKT_SEG, l.INDSEGA, l.INDSEGB, upper(substr(l.DRIVER,1,1)), l.WHse_LD_DATE
from tcsdw_parcustwc_feed l
where l.parent_name = k.parent_name)
Thanks to anyone who can respond!
-Jen
|
|
|
|
|
|
| Re: updating fields? [message #374606 is a reply to message #374605] |
Wed, 20 June 2001 16:30  |
Jen
Messages: 23
Registered: June 2001
|
Junior Member |
|
|
NOPE, why what does that do?
SQL> update tcsdw_par_cust_wc_st1 k
2 set (k.parent_name, k.MTPVF, k.EXIST_NEW,
k.PAR_WORK_CTR,
3 k.MKT_SEG, k.INDSEGA, k.INDSEGB, k.DRIVER,
k.WHse_LD_DATE) =
4 (select l.parent_name, l.MTPVF, l.EXIST_NEW,
l.PAR_WORK_CTR,
5 l.MKT_SEG, l.INDSEGA, l.INDSEGB,
upper(substr(l.DRIVER,1,1)), l.WHse_LD_DATE
6 from tcsdw_parcustwc_feed l
7 where l.parent_name = k.parent_name(+));
where l.parent_name = k.parent_name(+))
*
ERROR at line 7:
ORA-01705: an outer join cannot be specified on a
correlation column
should i move the where to outside the select?
|
|
|
|
|
|
| Re: updating fields? [message #374608 is a reply to message #374606] |
Wed, 20 June 2001 16:40 |
Jen
Messages: 23
Registered: June 2001
|
Junior Member |
|
|
Now I get a view error, I think.
1 update tcsdw_par_cust_wc_st1 k
2 set (k.parent_name, k.MTPVF, k.EXIST_NEW,
k.PAR_WORK_CTR,
3 k.MKT_SEG, k.INDSEGA, k.INDSEGB, k.DRIVER,
k.WHse_LD_DATE) =
4 (select l.parent_name, l.MTPVF, l.EXIST_NEW,
l.PAR_WORK_CTR,
5 l.MKT_SEG, l.INDSEGA, l.INDSEGB,
upper(substr(l.DRIVER,1,1)), l.WHse_LD_DATE
6 from tcsdw_parcustwc_feed l)
7* where k.parent_name(+) = l.parent_name
SQL> /
where k.parent_name(+) = l.parent_name
*
ERROR at line 7:
ORA-00904: invalid column name
It doesn't see l.parent_name because it's a
reference from the view used in the set statement.
Is there a way to get around this?
-Jen
|
|
|
|
| Re: updating fields? [message #374631 is a reply to message #374604] |
Fri, 22 June 2001 00:52 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Jen,
I am sorry to say this, but I guess you and all other replied ones forgot one thing that finding out first whether there is any unrelated row existing in the updated table. It may be possible that, the 426 row table has all the valid rows those have parent names match in the 409 row table. First you check whether there is any row in the first table that does not have any matching parent name in the second table. Use this first.
SELECT * FROM TABLE_426 WHERE PARENT_NAME NOT IN (SELECT PARENT_NAME FROM TABLE_409);
If it does not list out any row, your query is alright I say. Since all the rows have their corresponding in the second table, then all the rows should have been updated successfully in the first 426_tablle. But as you say the parent_name is unique in both the tables, then the above SQL statement should have listed out some unique rows those do not have matches in the 409_table and the update should not have taken place for all 426 rows. As I guess the parent_name is unique only in the 409_table, but not in the 426_table. Is it right? If it is so and the above SQL did not list out any rows, your query was perfectly alight I believe. Make sure again. Please let me know your output.
Thanks,
Sudhakar.
|
|
|
|
| Re: updating fields? [message #374632 is a reply to message #374604] |
Fri, 22 June 2001 00:53 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Jen,
I am sorry to say this, but I guess you and all
other replied ones forgot one thing that finding
out first whether there is any unrelated row
existing in the updated table. It may be possible
that, the 426 row table has all the valid rows
those have parent names match in the 409 row
table. First you check whether there is any row in
the first table that does not have any matching
parent name in the second table. Use this first.
SELECT * FROM TABLE_426 WHERE PARENT_NAME NOT IN
(SELECT PARENT_NAME FROM TABLE_409);
If it does not list out any row, your query is
alright I say. Since all the rows have their
corresponding in the second table, then all the
rows should have been updated successfully in the
first 426_tablle. But as you say the parent_name
is unique in both the tables, then the above SQL
statement should have listed out some unique rows
those do not have matches in the 409_table and the
update should not have taken place for all 426
rows. As I guess the parent_name is unique only in
the 409_table, but not in the 426_table. Is it
right? If it is so and the above SQL did not list
out any rows, your query was perfectly alight I
believe. Make sure again. Please let me know your
output.
Thanks,
Sudhakar.
|
|
|
|
| Re: updating fields? [message #374633 is a reply to message #374604] |
Fri, 22 June 2001 00:55 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Jen,
I am sorry to say this, but I guess you and all
other replied ones forgot one thing that finding
out first whether there is any unrelated row
existing in the updated table. It may be possible
that, the 426 row table has all the valid rows
those have parent names match in the 409 row
table. First you check whether there is any row in
the first table that does not have any matching
parent name in the second table. Use this first.
SELECT * FROM TABLE_426 WHERE PARENT_NAME NOT IN
(SELECT PARENT_NAME FROM TABLE_409);
If it does not list out any row, your query is
alright I say. Since all the rows have their
corresponding in the second table, then all the
rows should have been updated successfully in the
first 426_tablle. But as you say the parent_name
is unique in both the tables, then the above SQL
statement should have listed out some unique rows
those do not have matches in the 409_table and the
update should not have taken place for all 426
rows. As I guess the parent_name is unique only in
the 409_table, but not in the 426_table. Is it
right? If it is so and the above SQL did not list
out any rows, your query was perfectly alight I
believe. Make sure again. Please let me know your
output.
Thanks,
Sudhakar.
|
|
|
|
Goto Forum:
Current Time: Sat Jul 06 09:08:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
