Home » SQL & PL/SQL » SQL & PL/SQL » Query using (10g )
Query using [message #414286] Tue, 21 July 2009 06:49 Go to next message
vishal.2881
Messages: 2
Registered: July 2009
Junior Member
Hi All,


I move records from one table to another. from "tg" to "ts".

But before inserting , I need to search for a column value. example:

SQL> select * from tg;

        NO A          B          C                BKAC
---------- ---------- ---------- ---------- ----------
         1 y          a          yy
         2 y          b          yy
         3 x          b          yy


need to move data from "tg" to "ts".

Now, before moving the data need to find tg.bkac value using some other table.
i.e tb

SQL> select * from tb;

        NO A          B          C                BKAC       ORDR
---------- ---------- ---------- ---------- ---------- ----------
         1 y                     yy                  3         10
         2 y                     yy                  4         20
         3 x                                         5        100
         4 x                                        15        200


for ts.no =1 row, we get two rows in "tb" table. Null values in "tb" are considered are nullified using NVL. So we get two rows.

SQL> select * from tb;

        NO A          B          C                BKAC       ORDR
---------- ---------- ---------- ---------- ---------- ----------
         1 y                     yy                  3         10
         2 y                     yy                  4         20

From the above rows I alwaz want to assign Bkac to tg record for the min (ordr) i.e. for row tg.no =1 bkac assigned is "3" as from above rows.


currently i achieve this using a loop,but there are many records around millions. so need to do it using insert...select.




[edited because I'm bored of looking at unformatted code]

[Updated on: Tue, 21 July 2009 07:15] by Moderator

Report message to a moderator

Re: Query using [message #414289 is a reply to message #414286] Tue, 21 July 2009 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what are the requirements.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Query using [message #414292 is a reply to message #414286] Tue, 21 July 2009 07:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your post makes very little sense I'm afraid.

As far as I can tell, you want to copy data from one table to another, while modifying the data on the way.

If you can write a query that will return the data that you want, then simply do:
INSERT INTO <table> (<Column_list>) SELECT <your query>


I can be more help when you explain what you want better.
Re: Query using [message #414297 is a reply to message #414292] Tue, 21 July 2009 07:41 Go to previous messageGo to next message
vishal.2881
Messages: 2
Registered: July 2009
Junior Member
Hi,

I am trying to move data from one table to another.


example:


SQL> select * from tg;

        NO A          B          C                BKAC
---------- ---------- ---------- ---------- ----------
         1 y          a          yy
         2 y          b          yy
         3 x          b          yy




Now, from table "tg" i need to move data to "ts" having same structure. Using insert...select. Check for the structure "ts"


SQL> desc ts;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                                 NUMBER
 A                                                  VARCHAR2(10)
 B                                                  VARCHAR2(10)
 C                                                  VARCHAR2(10)
 BKAC                                               NUMBER




Before moving, I need to assign value for tg.bkac using some other table "tb"


SQL> select * from tb;

        NO A          B          C                BKAC       ORDR
---------- ---------- ---------- ---------- ---------- ----------
         1 y                     yy                  3         10
         2 y                     yy                  4         20
         3 x                                         5        100
         4 x                                        15        200




In above table, we will get two rows in table "tb" for tg.no = 1 by nullifying the "NULL" values. example:


SQL> select tg.a,tg.b,tb.c,tb.ordr,tb.bkac from tg, tb 
  2  where nvl(nvl(tb.a,tg.a),1) = nvl(tg.a,1) 
  3  and nvl(nvl(tb.b,tg.b),1) = nvl(tg.b,1) 
  4  and nvl(nvl(tb.c,tg.c),1)  = nvl(tg.c,1) ;
  5 and tg.no =1

A          B          C                ORDR       BKAC
---------- ---------- ---------- ---------- ----------
y          a          yy                 10          3
y          a          yy                 20          4




from the above rows I alway want bkac with min(ordr) to be assigned to the row i.e. ordr = 10 and bkac = 3




SQL> select * from tg;

        NO A          B          C                BKAC
---------- ---------- ---------- ---------- ----------
         1 y          a          yy                 3
         2 y          b          yy                 3
         3 x          b          yy



Edited by: user11715427 on Jul 21, 2009 5:39 AM
Re: Query using [message #414301 is a reply to message #414297] Tue, 21 July 2009 08:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 21 July 2009 13:56
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Previous Topic: Performance Tuning
Next Topic: Query to display a column value into multiple rows[Merged]
Goto Forum:
  


Current Time: Sun Dec 04 00:28:36 CST 2016

Total time taken to generate the page: 0.11816 seconds