This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / One question about Oracle.The relationnship between Table1 and Table2 is one to multi.
Now I want to copy data from Table2.field1 to Table1.field1.
But now, I need to merge the data in Table2.field1 before I copy this data into Table1.field1.
For example, for one related item: Table2.Field1 has "1","2","3","4" values, now I need to copy "1,2,3,4" in to Table.Field1.
Must I write program to solve this problem, or there is easy way to do this just by sql in oracle?
-lilyba(Sunshine);
2002-3-5
{456}
(#390984@0)
-
So easy ! Think it by yourself !
-edba(full-time DBA);
2002-3-5
(#391006@0)
-
Write a store procedure if possible. I already feel the pain of terrible design ...:-)
-x2000(围城专家);
2002-3-5
(#391010@0)
-
Just SQL Statement is OK
-edba(full-time DBA);
2002-3-5
(#391013@0)
-
Ok, then tell me. I am new to Oracle.
-lilyba(Sunshine);
2002-3-5
(#391017@0)
-
insideinsert into table1(filed1,filed2,...)
select exp1,t2.fld2,...
from (select fld2 from table2 where ...) t2
where ...
-edba(full-time DBA);
2002-3-5
{118}
(#391027@0)
-
看起来你是在工作,还是用个store procedure吧, 我记得单行SQL是不行的
-axinsoft(过河卒子-炎);
2002-3-8
(#395060@0)
-
Do you mean create one string which contains all distinct values of table2.field1 and then insert into table1 as one row?
-mickeymouse(Part Time DBA);
2002-3-5
(#391038@0)
-
try this statement: inert into table1(field1) select distinct field1 from table2; (it is correct when table1 has no other not null columns)
-laotang(OracleFan);
2002-3-8
(#394918@0)
-
Hi, Thanks for answering my question about oracle. I tried the following method but not successful.Do you mind have a look at my SQL to help me find out what is the problem?
update inventory
set il1 =
(select exp1, t2.itemnum
from
(select invvendor.itemnum from invvendor) t2
where
inventory.itemnum=t2.itemnum)
relationship between inventory and invvendor is
inventory.itemnum(1)----(multi)invvendor.itemnum
I want to copy catalog from invvendor to inventory
-lilyba(Sunshine);
2002-3-8
{378}
(#394929@0)
-
This SQL statement can not work. I need more information about your question.Please use : DESC <your tablename>;
to list these two tables' structure.
Then, Tell me really what you want to do.
Merge data or select data or sort data.
I'll wait your feedback.
-kew888(Richard);
2002-3-8
{191}
(#394943@0)
-
here is details. thanks.
-lilyba(Sunshine);
2002-3-8
{5161}
(#394977@0)
-
OK. I think you need a loop to handle this problem.If it is number, we can sum it up. But for varchar, we need to append it
one by one. In this case, we need loop function to do that because the
update only run once.
-kew888(Richard);
2002-3-8
{167}
(#395039@0)
-
thanks
-lilyba(Sunshine);
2002-3-8
(#395055@0)
-
Your SQL might look like this. Because:update inventory
set il1=
(
select inventory.exp1
from
(select itemnum from inwendor) t2, inventory
where
inventory.itemnum = t2.itemnum
);
Note: You want to update one field (il1) so you will not select two
fields in your sub-query.
Wish this can help you.
-kew888(Richard);
2002-3-8
{276}
(#394968@0)
-
Update doesn't work at this case. Why not try Insert?
-threetree(落霞孤鹜);
2002-3-8
(#394994@0)
-
they are not new records.
-lilyba(Sunshine);
2002-3-8
(#394998@0)
-
如果table2中filed1的值不可以作table1中的PK怎么办？
-edba(full-time DBA);
2002-3-8
(#394987@0)
-
you can not do it in one simple SQL statement, you can try T-SQL or PL-SQL,
which means stored procedure solution.Straight SQL can not do a pivot table query with an unknown number of columns.
One can do a generalized pivot table query using either a cursor or T-SQL (T-SQL
supports the WHILE statement which enables one to loop). But, to repeat, one
SELECT statement by itself can not produce a generalized pivot table. BTW, this
is not just my opinion, this is proven mathematical fact.
-interview(intervieweree);
2002-3-9
{380}
(#395609@0)
-
hope this one will help you.
-interview(intervieweree);
2002-3-9
{11215}
(#395621@0)