SQL & PL/SQL :: Join In Insert - Multiple Tables
May 20, 2013
I am trying to insert records in multiple tables. I know how to view data using joinig, but unable to understand how to insert records in multiple tables using Joining. I searched it on net, but didn't find much. I have also tried to write a code, but it is not working, I have seen some examples on different websites where people are using SELECT in INSERT statement for joining. What is the correct Syntax to INSERT record in Multiple tables.
Insert into library_users, library_users_info
(library_users.username, library_users.password, library_users_info.address, library_users_info.phone_no) VALUES (...)
View 2 Replies
ADVERTISEMENT
Mar 29, 2011
Here is what I'm trying to do:
I have three tables:
TABLE1 TABLE2 TABLE3
NCI 1AB OPEN
NCI (NULL) (NULL)
INV (NULL) (NULL)
NCI 2AB REOPEN
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU",
l.loc AS "LOC",
l.qty AS "QTY",
o.ncikey AS "NCI",
r.description AS "NCI DESC",
o.qtyexpected AS "NCI QTY EXP",
o.qtyreceived AS "NCI QTY REC",
o.loc AS "NCI LOC",
o.status
[Code]...
View 7 Replies
View Related
May 27, 2010
I have 8 tables and I want full outer join on these to get the output. The tables are very small having 10 rows at max and consists of only two columns (date and value).
how to write query for this.
View 8 Replies
View Related
May 29, 2013
We get data from our customers which we load into temporary tables.The goal is to consolidate this data into one single table.
Following are the rules:
1) final table should have all the columns from all the tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)
4) There could be 'N' number of tables
Following is the most realistic data.
1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4
5) the only information we have to work with is the TABLE_NAME(s) and its metadata from USER_TAB_COLUMNS
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;
[code].....
Explanation for each row:
row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3
View 5 Replies
View Related
Apr 9, 2011
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
View 15 Replies
View Related
Jun 8, 2010
I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
View 4 Replies
View Related
Jan 2, 2013
how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?
View 4 Replies
View Related
Jul 9, 2012
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
View 6 Replies
View Related
Aug 13, 2011
I am working on a query that has outer joins. The result of this query has to be stored into a table. When i execute this query alone (without INSERT command) it works absolutely fine but when i put the INSERT statement it gives me the error message "ORA-01719: outer join operator (+) not allowed in operand of OR or IN". The thing is I dont have an IN or OR in my query. I am in the middle of something
Attached is the screen shot of both the result and the error message. Query mentioned below:
<INSERT into table query comes here..>
select * from (
SELECT yy.ACCOUNTID, yy.dump_date, yy.balance, yy.Last_30days_activity,zz.GSM_Balance, nvl(yy.SUM_GSM_REV, 0) from
(
[Code]....
View 7 Replies
View Related
May 17, 2010
oracle 10g to select column from about 8 table . I start with this statement
select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h
from A
full outer join B on(A.a=B.b)
full outer join C on(B.b=C.c)
full outer join D on(C.c=D.d_
.
.
..
.
.
.
View 12 Replies
View Related
Apr 12, 2010
I have a table that contains 15 columns. Say each columns contains name of fruits. And another master table which contains names of valid fruits. Now I have to create another table from these tables such that for each column if the fruit name is present in the master table then fruit-name is populated else "invalid" is populated. I know using left join for 15 times to the same master table can work. But I found this very clumsy. Is there any other way of doing it.This is just a sample case. In real scenario I have a table of 800 million records and master table of 30000 records.
View 7 Replies
View Related
Jul 29, 2013
I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
View 23 Replies
View Related
Aug 5, 2011
I have a question related with VIEWS
I would like INSERT data in a VIEW with a INNER JOIN, like this example:
CODECREATE VIEW MYVIEW (order_id,list_price,customer_id) AS
SELECT order_id, list_price, customer_id FROM ORDERS o
INNER JOIN PRODUCT_INFO p ON (o.order_id= p.pdt_id);
INSERT INTO MYVIEW VALUES (4,500,10); /* will cause an error*/
But when I try to execute the insert statement, the "SQL Developer" returns a error:
ORA-01779 - "cannot modify a column which maps to a non key-preserved".
Why can't I do it? Are there some way to do it?
View 3 Replies
View Related
Apr 28, 2010
I am little confused applying DISTINCT on a Multiple table Inner join.
Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com
Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4
Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4
Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4
QUERY::
select distinct c.process_id a.role_id,a.email_address,c.process_name
from role a, notification_role b, process c
where a.role_id=b.role_id and b.process_id = c.process_id
View 3 Replies
View Related
Sep 7, 2010
how to join 2 tables to get the result I need.I have following 2 tables (all dummy data)
Table 1
org_iddd_1dd_2dd_3
K87973789865879876432465
J87879454321981234723454
Table 2
acc_numacc_nameacc_typevol_sales
789865 Abcn185765
879876 defd494854
432465 efgg5948545
789865 hijh685765
879876 klmj794854
432465 nopl9948545
I want to join the above tables to get the following result
org_idacc_numvol_sales
K8797378986585765
K8797487987694854
K87975432465948545
J8787978986585765
J8788087987694854
J87881432465948545
I have tried writing union queries and exists clause in where and seem to get nowhere with this.
View -1 Replies
View Related
Sep 11, 2013
CREATE TABLE MTCH_TBL
(
S_PAID VARCHAR2(100),
S_SEC NUMBER,
H_PAID VARCHAR2(100),
H_SEC NUMBER
);
[code]....
My requirement is to match the records in MTCH_TBL and HIST_TBL on the basis of joining S_SEC(MTCH_TBL) with SEC_ALIAS(HIST_TBL) and INSTANCE (HIST_TBL) as 100 and choosing the record with max EFF_DATE from HIST_TBL.
I have come up with a query as:
select a.h_sec, a.s_paid, a.h_paid
FROM MTCH_TBL a,
HIST_TBL b
where a.S_SEC=b.sec_alias(+)
and b.instance(+)=100
and b.EFF_DATE =
[code]...
join using the EFF_DATE field also and get the expected results.My results are appearing as BLANK. However I need to produce the results as stated below:
Now, i need the results as:
H_SEC= 526
S_PAID= Q00
H_PAID = Q00
View 39 Replies
View Related
Apr 28, 2010
I am trying to write an Update that really frustrates me because it won't work for one reason or another.The situation is that I have two tables for customer information, t1 with the names of the customer and t2 with the address.These two can be joined via a client_id.
Now I have a third table t3 with the name and address of potential customers. I want to find out if some of them are already known to me so that I can update the client_id from table t1 or t2 into t3.
I have to join firstname, lastname from t3 to firstname, lastname from t1 and street, zip, city from t3 to street, zip, city from t2 and client_id from t1 to t2. Additional there is the problem that there can be more than one result so I have to update one of the found client_ids per name/address into t3.I am no expert to PL/SQL, I just know what SQL works in Access and that is:
UPDATE (t3 INNER JOIN t1
ON (t3.firstname= t1.firstname) AND (t3.lastname = t1.lastname)) INNER JOIN t2
ON (t3.city = t2.city) AND (t3.zip = t2.zip) AND (t3.street = t2.street) AND (t1.client_id = t2.client_id)
SET t3.client_id = t1.client_id;
View 2 Replies
View Related
Dec 18, 2012
I'm looking a way to easily join two tables on date value but right table has no all possible dates so I need t fill missing records with MAX(DT) values.
My tables looks like
CREATE TABLE CAL
(DT DATE NOT NULL
);
INSERT INTO CAL
(DT
[code]......
And I was trying to merge them somehow to get following result (for all missing dates from SOME_DATA I want to get value corespond to maximum data not higher than cal.dt):
-- good result
| CAL.DT | SOME_DATA.SOME_VAL |
-----------------------------------
| 01/01/2012 | 10 |
| 02/01/2012 | 10 |
| 03/01/2012 | 15 |
| 04/01/2012 | 15 |
| 05/01/2012 | 15 |
[code]......
View 6 Replies
View Related
Jan 23, 2013
I'm wondering what part of this query is wrong because obviously I'm not getting the desired results.
select a.iss_id,C.ISSR_ID
from ft_t_isid a left outer join ft_t_issu b on a.INSTR_ID=b.INSTR_ID
left outer join ft_t_irid c on b.INSTR_ISSR_ID=c.INSTR_ISSR_ID
and a.ISS_ID in ('CA13606ZDD20',
'CA780086AP98',
[Code]...
I know that the problem is in the joins. I expect to get 9 rows of result but I get a lot more and they are not even what I'm looking for.
View 19 Replies
View Related
Jan 2, 2009
I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...
Why I'm getting the error "ORA-00947: not enough values"?
INSERT ALL INTO mtl_system_items_interface
VALUES
(process_flag,
transaction_type,
set_process_id,
[code].......
View 3 Replies
View Related
Sep 14, 2012
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
View 1 Replies
View Related
Feb 14, 2012
I'd like to join tables in a cursor.
I tried coding this way...
CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, subj_code, s.description
FROM studgrades g JOIN subjects s
ON(g.subj_code = s.subj_code)
WHERE stud_id = :Studentprofile.student_id;
but i got an error, saying:
Quote:encountered the symbol JOIN when expecting one of the following:
,; for group having intersect minus order start union where
connect
Is it not allowed to use JOIN statement in a cursor?
View 4 Replies
View Related
Jan 15, 2013
I have two permanent tables. I want to add the column DELTA_STROM from the table "Delta" into the table "TEST2". Here, the value of the field "POWER_DELTA" in the table "TEST2" depends on the field "Trade_Date".
If the time of the timestamp in table "Test2" is smaller than 12:40 than the value (DELTA_STROM from DELTA) of the day must be added into the field. If the time is huger than 12:40, the value of the next day must be added into the field "POWER_DELTA". All values for "Power_DELTA" for every day are in table "Delta" in the field "DELTA_STROM".
I just added the right values of "Power_Delta" into the table "TEST2" manually to give an understanding.
DATUM DELTA_STROM
-------- -----------
01.01.12 1.92
02.01.12 5.78
03.01.12 0.73
04.01.12 2.84
05.01.12 11.41
06.01.12 -2.76
07.01.12 0.43
08.01.12 1.25
09.01.12 -0.21
TRADE_DATE POWER_DELTA
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000 0.73
02.01.12 14:05:01,845000000 0.73
02.01.12 16:21:01,345000000 0.73
02.01.12 17:25:41,470000000 0.73
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000 2.84
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000 11.41
CREATE TABLE "TEST2"
("TRADE_DATE" TIMESTAMP (6),
"POWER_DELTA" NUMBER
);
[Code]....
View 19 Replies
View Related
Jan 16, 2012
I have to do the optimization of a query that has the following characteristics:
- Takes 3 hours to process
- Performs the inner join with 30 tables
- Produces an output of 280 million records with 450 fields
First of all it is not feasible to make 30 updates (one for each table) to 280 million records.
The best solution that I had found so far was to create 3 temporary tables, where each of them to do the join with 1/3 of the 30 tables, and in the end I make the join between the main table and these three tables temporary.
I know that you will ask (or maybe not) to the query and samples, but it is impossible to create 30 examples.
how to optimize this type of querys that perform the join with multiple tables and produce a large output with (too) many columns.
View 15 Replies
View Related
May 13, 2013
I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan :
SELECT FIRST_NAME
FROM CAMPA.TABLE_A
WHERE NAME_ID =
(SELECT NAME_ID
FROM CAMPA.TABLE_B
WHERE ban=:b1);
[code]....
View 7 Replies
View Related
Mar 1, 2010
I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.
Table1:
col1col2category
A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1
Table2:
colAcolBActive
A10Y
A20N
B30Y
B40N
Z35Y
Table3:
STUDENTSCORE
A20
B40
C10
D20
J30
Z35
query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.
View 8 Replies
View Related
Nov 18, 2010
Trick is that I've got 4 tables
Sale , sale_details, sales_message, message_text
Table:Sales
Sales_id
Table: Sales_product
Sales_id, sales_product_id
Table: Sales_message
Sales_id, sales_message_id, message_text_id
Table:Message_text,
Message_text_id, message_text
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123
Product1 ID 1234 for sale ID 123
Product2 ID 4321 for sale ID 123
Sales_message ID 098 for product ID 1234 'blabla'
Sales_message ID 876 for product ID 1234 'albalb'
AND NO MESSAGES FOR PRODUCT 4321
This is what I want to get from DB.
View 3 Replies
View Related
Aug 16, 2010
I have two tables. i need to join the tables. The query is -
select v.c_venditore,v.s_venditore,v.t_diretto_indiretto,v.d_disattivazione,d.s_direzione from VENDITORE v,DIREZIONE d
where v.p_direzione=d.p_direzione order by v.s_venditore
In the table VENDITORE there are 2919 rows. I need to display all the rows. But the joining column p_direzione has some null values.I need to display the null also. But to join the two tables this is the only condition. How can i display all the rows.
View 3 Replies
View Related
Nov 2, 2013
I have 2 tables Table a(girlscoutid, item, quarter)Table b(girlscoutid, fname, lname) I want to get the names of ppl who did not sell any item/s for the
4th quarter
girlscoutid item quarter7771
drinks 4QS9000
tickets 4QW7771
cookies 2QS5085books 3QF3010tickets3QW5085
cookies1QF lscoutid
fnamelname7771 jenn richochet9000 laura wilkins5085sally mae3010dora mckenzie
This is what I have, however, I get duplicates and also 7771 shows up on the list even though she sold an item on the 4Q.
SELECT fname, lname, a.girlscoutid
FROM a,b
WHERE a.girlscoutid = b.girlscoutidAND term NOT LIKE '4Q_';
View 4 Replies
View Related
May 2, 2009
I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.
I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example
SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';
View 6 Replies
View Related
