PL/SQL :: Update Two Columns In TABLE2 From TABLE1
Jan 25, 2013
I know this is a simple question for some of you, but I am new to SQLs,
I have two tables TABLE1 & TABLE2 as below, both tables contains more then 50million records:
SELECT * FROM TABLE1.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
ID BUS_FID WORKID STATIONID
---------------------- ---------------------- ---------------------- ----------------------
28400000117234 245 13461428.25 16520877.8
28400000117513 403 13461428.25 16520877.8
28400000117533 423 13461428.25 16520877.8
28400000117578 468 13461428.25 16520877.8
28400000117582 472 13461428.25 16520877.8
SELECT * FROM TABLE2.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
BUS_FID ID TRPELID RELPOS WORKID STATIONID
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
114 28400000117658 28400000035396 23.225
115 28400000117659 28400000035396 23.225
116 28400000117660 28400000035396 23.225
117 28400000117661 28400000035396 23.225
118 28400000117662 28400000035396 23.225
119 28400000117663 28400000035396 23.225
120 28400000117664 28400000035396 23.225
[Code]....
Now I tried to use following SQL to update WORKID & STATIONID columns in TABLE2 but failed. BUS_FID in both tables have been UNIQUE indexed and they can be used as primary keys to join these two tables.
UPDATE (
SELECT p.WORKID px,
p.STATIONID py,
p.BUS_FID pid,
temp.WORKID tempx,
temp.STATIONID tempy,
[Code]....
with above code, Oracle returned following errors:
SQL Error: ORA-00904: "TEMPID": invalid identifier
00904. 00000 - "%s: invalid identifier"
BTW, both two tables contains over 50 million records. So, if you have a better SQL to perform the same task.
View 6 Replies
ADVERTISEMENT
Feb 20, 2012
I have two tables as
Table LEAVE
Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected
[code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle
AFTER INSERT ON LEAVES
FOR EACH ROW
BEGIN
UPDATE LEAVEENTITLE LVE
SET LVE.SL_USED = SL_USED+(CASE
WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED'
THEN :NEW.NO_OF_DAYS
SL_UNUSED=SL_ENTITLED - SL_USED
ELSE 0
END),
[code]....
View 9 Replies
View Related
Aug 3, 2010
there are a number of ways I can do this, but I'm just posting this here incase any of you plsql experts know of the best way to program this.
Basically I have 2 tables
INT_CASH_RECORDS
TMP_CASH_RECORDS
Both these tables have exactly the same number of fields and field types - both tables are literally the same. The primary key in both tables is a field called 'cash_id'
How can I transfer a record from INT_CASH_RECORDS into TMP_CASH_RECORDS based in a cash_id, I'm looking for the query string, something like
insert into tmp_cash_records (select * from int_cash_records where cash_id='3342' ...)
View 10 Replies
View Related
Sep 14, 2012
table1-- EMPLOYEE
empno ename dept
001 david 20
002 thomas 30
I need query to dump all the values to below table as show below
table2 -- DEPT
empno info
001 david
001 20
002 thomas
002 30
View 10 Replies
View Related
May 17, 2011
I got table alpha
CREATE TABLE alpha
(alpha_id NUMBER
alpha_cash NUMBER,
alpha_other VARCHAR2(20)
);
[Code]....
What is the best/a good way to display in alpha the total regarding the alpha_id ?
View 1 Replies
View Related
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
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
Mar 12, 2013
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin
merge into copy.table1 rr
using
(
select
ID ,
DEALID ,
ESTIMATIONDATE ,
BOUNDOVERESTIMATDATE ,
ESTIMATIONTYPEID ,
MARKETAMOUNT ,
LIQUIDATINGAMOUNT ,
[code]....
View 2 Replies
View Related
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
Dec 20, 2010
My requirement is that under table name like A I have to update remaining empty columns as A and under B remaining null columns as B and so on.
View 14 Replies
View Related
Sep 14, 2011
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1
=====
license_nbr(pk) name address
=============== ===== =======
LicNo1 test testing
Table2
=====
ticket number(pk) amountto be paid balance_amount license_nbr(fk table2)
tk1 200 200 0 LicNo1
tk2 300 300 0 LicNo1
tk3 400 400 0 LicNo1
tk4 500 500 400 LicNo1
table3
=====
payment table
ticket_number(fk table2) amount paid payment status license_nbr(fk table1)
tk1 1000 excess paid. LicNo1
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
View 14 Replies
View Related
Mar 15, 2010
I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48
Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72
this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
View 4 Replies
View Related
Dec 13, 2011
how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
View 8 Replies
View Related
Jun 19, 2013
I have more than 10 lakhs records in the table for which i am going to update two columns without any filtration. i have pasted my query in it..it's taking more time to update..is there any way to fine tune this block.
DECLARE
l_fallback_page Au_Case_Parallel_19062013.page_num%TYPE;
l_fallback_kwd Au_Case_Parallel_19062013.Fallback_keyword%TYPE;
lv_type varchar2(1000);
[Code]....
View 9 Replies
View Related
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
View Related
Jun 27, 2012
I have in a plsql block somewhere a statement like
INSERT INTO TABLE1( id , col)
SELECT id, col
FROM TABLE2;
This statement returns an error ORA-00001: unique constraint because id is a primary key on TABLE1. I would like to know what is the value of id that raised the exception.
View 15 Replies
View Related
Dec 8, 2010
I have three tables fixtures, fixture_teams and team_tbl
fixtures consists of:
create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....
creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.
View 13 Replies
View Related
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
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
Sep 2, 2013
I am usingOracle 12cODAC 12.1.010 and when make an update on table with virtual column i have this error:ORA-54017: UPDATE operation disallowed on virtual columns. I have seen that on edmx file the virtual column, the StoreGeneratedPattern property is not setted as Computed. Setting it (handly) works ok. Is a bug of ODAC?
View 1 Replies
View Related
Apr 25, 2012
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
View 13 Replies
View Related
May 31, 2010
I would like to UPDATE the columns p1 and p2 of my table student (studentid:pk,name,p1,p2,...) for a given studentid.and I have a when-button-pressed trigger with this
UPDATE student
SET student.p1=:validation.proj1,
student.p2=:validation.proj2
where UPPER(student.studentid)=UPPER(:validation.studentid);commit_form;
when I run my form with a correct studentid, I got this error: FRM-40508: ORACLE error: UNABLE to INSERT record
but it is cworking correctly in sqlplus; and I have all priveligies.
View 4 Replies
View Related
Aug 13, 2011
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
View 4 Replies
View Related
Feb 6, 2011
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
View 4 Replies
View Related
Jun 16, 2010
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies
View Related
Apr 4, 2011
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
View 12 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
