PL/SQL :: Merge / Update With Multiple Tables And Columns

Sep 6, 2013

I am trying to update multiple columns from one table based on the results of another table So I have 3 tables as follows 

HISTORYSUMM_SNAPADM_CHOICE 
My SQL code is loosely SELECT SUM(H.HIS1),
SS.SNAP1,
AC.ADM1FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE ACWHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2GROUP BY SS.SNAP1,
AC.ADM1

 This works, and I am able to SUM the column as I need with the right numbers.  I altered the SUMM_SNAP table and now I want this summarized column to be in the table I tried using UPDATE, but there is no FROM clause to let me do the table join/group by 

UPDATE SUMM_SNAPSET SUMM_SNAP.SNAP3=SUM(H.HIS1)FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE AC
WHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2
GROUP BY SS.SNAP1, AC.ADM1 

The above is obviously wrong - but just trying to show whatI was thinking What would be the best method to get the numbers from the SUM into a table?

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Cursor Select For Update / Multiple Columns Of Different Tables

Apr 8, 2010

i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.

I have a code written as follows :

DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......

The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].

It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.

View 5 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Merge Columns Of 2 Tables?

Apr 14, 2011

i wanted the query for the followin example

sample1
A B C D E F
--------------------------
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5

sample2
G H I J
----------------
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5

consider the above tables what i want is some thing like this

output
G H I J A
-----------------

this can also be done through a select statement through choosing the columns which you want i don want thatbut what i want is columns of entire table sample2 and only one column in sample1

View 5 Replies View Related

PL/SQL :: Update Multiple Columns With Single Update Statement

May 30, 2013

i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.

View 5 Replies View Related

SQL & PL/SQL :: How To Update Multiple Columns

Dec 12, 2011

how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this

update country1 set cname='japan','usa'
where cid=100,101

ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION

View 6 Replies View Related

SQL & PL/SQL :: Using Cursor To Update Multiple Columns

Oct 22, 2011

I need to write a script which copies 4 col data from one table to another table. there are three tables

cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.

cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.

So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.

View 20 Replies View Related

SQL & PL/SQL :: Forall Update With Bulk Collect For Multiple Columns

Jan 25, 2013

I am trying to update a table column values if any change occurs using bulk collect and for all update not able to get idea. below is the proc working out.it is for insert and update using the cursors.

CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS
TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE;
V_PARAM T_TEST_TAB;
V_STATUS NUMBER;
V_BUS VARCHAR2(20);
V_UP VARCHAR2(1);
V_Q VARCHAR2(50);

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Update Statement Using Multiple Tables

Aug 26, 2010

I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;

update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword

View 4 Replies View Related

SQL & PL/SQL :: After Update Trigger (with Multiple Tables)

Nov 21, 2012

I have to write a "after update trigger". Here, i have to update the stock table by other inventory tables (by complex query).
I have written trigger below. how to make it correct?

create or replace trigger trg_stk_upd_pur
after update on O_STOCK_EFFECTS
REFERENCING NEW AS new OLD AS old
FOR EACH ROW

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: Procedure To Update Or Insert Data In Multiple Tables

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

Merge Over Multiple Schema

Aug 31, 2007

Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D

Code excerpt
---------------------
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, ...

Permissions given are as follows

Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
----------------
ViewB - Select on User A, Select on UserARole

I believe appropriate permissions have been given.

View 1 Replies View Related

SQL & PL/SQL :: Merge Multiple Rows Into One Row

Apr 7, 2011

How am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus.

My current result set:
user id|user name|group name
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3

What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3

View 7 Replies View Related

PL/SQL :: Merge Statement - Update Only When There Is Difference?

Feb 7, 2013

I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2

Here is the MERGE statement:

Merge into tb_trgt trgt using tb_src src
on (src.id = trgt.id)
when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm)
when matched then update set trgt.nm = src.nm, trgt.addr = src.addr
where trgt.nm <> src.nm or trgt.addr <> src.addr
;

Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.

View 3 Replies View Related

SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?

Jul 12, 2006

I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.

SQL how can we do this in a simple way? each tables has around millions records.

View 4 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

SQL & PL/SQL :: MERGE INTO - Unable To Get Stable Set Of Rows In Source Tables

Aug 30, 2010

When i am running the below merge statement it gives the error as below

MERGE INTO non_tab tt
USING (SELECT non_tab.acct,
non_tab.mis,
non_tab.n_gaap_skey,
non_tab.run,
non_tab.mit_ul AS t0,
CASE
WHEN (( CASE
[code]...

ORA-30926: unable to get a stable set of rows in the source tables.

View 5 Replies View Related

SQL & PL/SQL :: Merge Statement - Unable To Get Stable Set Of Rows In Source Tables

Sep 15, 2013

When I am executing merge statement I am getting the below error.

ORA-30926: unable to get a stable set of rows in the source tables

MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,

[Code]....

The query is fectching below data.

SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131

[Code]...

Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.

When I run the same merge statemment second time I am getting the below error.

ORA-30926: unable to get a stable set of rows in the source tables

So that I have used partition by cluase in the slect statement and I am able to resolve the error.

But it's inserting only 14 records not all 15. How to process all 15 records without the error..

View 16 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

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

SQL & PL/SQL :: How To Update Multiple Rows With Different Values Using Update Statement

Mar 21, 2011

I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.

Eg:-

SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250

[Code]....

The above table contains 14 records. Now i would like to update the salary column with different values like

EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000

How to update above values with single update query.

View 11 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

Jul 19, 2011

I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).

View 4 Replies View Related

Reports & Discoverer :: How To Merge Multiple Reports In One Report In 6i

Aug 23, 2011

How to merge multiple reports in one report in reports 6i.

View 1 Replies View Related

SQL & PL/SQL :: Merge Query / All Three Tables Having Same Conditions And Filter Conditions

Apr 20, 2011

SELECT
MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE,
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS,
MAX(fnddoc.description) as REASON
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
[code]....

I have three tables, I have to merge those three tables, all three tables having same conditions and filter conditions(in each table one filter condition changed), I highlighted in the red difference the filter conditions in each table, finally my result should be 7 columns like

LAST_UPDATE_DATE, COMMENTS, REASON, CORRECTD_ACTUAL_DELIVERY_DATE, LAST_UPDATE_DATE, CORRECTD_PROMISE_DATE, LAST_UPDATE_DATE

View 2 Replies View Related

SQL & PL/SQL :: Sort On Multiple Columns

Jun 16, 2010

how does sorting on multiple columns work

suppose my query is

select * from person order by first_name desc
and sys_person_id asc

this query works , but is this write way to sort on multiple column ?

View 12 Replies View Related

SQL & PL/SQL :: Order By Multiple Columns

Oct 9, 2013

I have a query to pull the first contact of students.

The table has all contacts like parent/guardian, friends family, emergency contact etc.

I would like to the first primary contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.

I don't know how to pull 2 and 3 . because it looks like it needs to concatenate the columns.

here is my initial query

Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts

how to achieve 2 and 3?

The table script is attached. All the above columns are 1 or 0.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.

View 8 Replies View Related

PL/SQL :: Multiple Columns In IN Clause?

Jun 19, 2013

select * from nrc_trans_descr where type_id_nrc=60013   -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
 
TYPE_ID_NRC  TRIGGER_STATUS  INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
      60013              0                 61013                  
i have to pass TYPE_ID_NRC  and  INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this 
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in (  select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in (  select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));

am using ORACLE 10GR2(solution for 11gr2 is welcome too) 

View 16 Replies View Related

SQL & PL/SQL :: Query With Multiple Columns

Dec 17, 2012

Table-Name

ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9

select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));

Result should be " Frog and Elephant and tiger"

Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )

Note:

tracking IDs may change up on error

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved