SQL & PL/SQL :: Update Statement - Calculating Few Values From Large Table

Sep 2, 2011

I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:

I want to calculate the VALUE_LAG with ID = 4 only (-> two values).

create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );

[Code]..

I tried this, but obviously, windows functions are not allowed in the update statement.

update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4

How can I do this?

View 12 Replies


ADVERTISEMENT

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

Update Multiple Rows With Different Values In A Single Statement

Jul 24, 2009

Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.

Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.

Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.

MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.

Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row

UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))

Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.

View 1 Replies View Related

SQL & PL/SQL :: Update Statement For OLTP Table

May 14, 2013

I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.

The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column

--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......

--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......

View 4 Replies View Related

SQL & PL/SQL :: Update Table With Values

May 7, 2010

I have a table called 'price' and want to update it with a table called 'update'. See workbook in the attachment.

The idea is to update the table 'price' with values from 'update' as long as the values from 'update' are different then the values in 'price' or the value in price with 'enddat' empty is not present in 'update'. you can still follow this...

Example:

Enddat for df_location 4 from 'price' is empty and df_location 4 is not present in 'update' so 'enddat' for this df_location should be filled with any date from 'update' (there is always going to be just one distinct date).

I have tried something like this:

proc sql;
insert into work.price (enddat) where enddat = ''
select (date) where df_location not in (select
df_location from work.update);
;
quit;

Well this obviously doesn't work. I'm trying to pass 2 criteria (when enddat is empty and df_location is not present in 'update') but I'm not sure where to put them...

View 3 Replies View Related

SQL & PL/SQL :: Oracle Large Update

Sep 30, 2011

I need to add a new column to a very large table, and update it with 'N'. (this is similar as specifying default 'N'). I'm using Oracle 9i. Which is the best method regarding to speed, to update this column on entire table? The table contains ~30 millions of records. I've read that parallel DML (here UPDATE) does not work on unpartitioned tables. My table is not partitioned. If i specify:

update /*+ full(p) parallel(p,10) */ my_table p set p.my_column = 'N';

This, i think will not speed up the operation on 9i. Our business does not accept to use CREATE TABLE AS SELECT, then renaming table and recreating all indexes and so on.

View 21 Replies View Related

SQL & PL/SQL :: FOR UPDATE Large Data

Jan 23, 2012

the large data FOR UPDATE in table column ?

claimClob clob:=claim; -- claim large data
v_buf varchar2(1000);
amount binary_integer:=1000;
position binary_integer:=1;

[Code]....

why the FOR UPDATE don't do nothing ?

View 4 Replies View Related

SQL & PL/SQL :: Query To Store Results - Update Values In Another Table?

Feb 22, 2010

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;

When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.

View 6 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

Performance Tuning :: Update Million Rows In One Table With Values From Another Tables?

Feb 15, 2011

I am trying to update a million rows in one table with the values from another tables.

Table being updated CI_ADJ_CHAR column CHAR_VAL_FK1
Table from which values will be used CK_ADJ columns (cx_id, ci_id)

The CI_ADJ_CHAR.CHAR_VAL_FK1 values match CK_ADJ.CX_ID and should be updated with the value CK_ADJ.CI_ID.

The CK_ADJ table has 1.3 million rows and both the columns have indexes defined. Table definitiuon mentioned below

The CI_ADJ_CHAR table has 14 million rows and will update 1 million rows and has an index on the ADJ_ID column but not on the CHAR_VAL_FK1 column.

View 1 Replies View Related

PL/SQL :: Update Table With Values From Another Table

Dec 19, 2012

I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:

CREATE TABLE TEST1 (
     COD NUMBER,
     DCR VARCHAR2(10) );

CREATE TABLE TEST2 (
     COD NUMBER,
     DCR VARCHAR2(10),
     DCR2 VARCHAR2(10) );
[code]....

I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:

SELECT * FROM TEST1

COD --- DCR
------------------
1 'TESTE3'

View 6 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related

Update Statement Versus Cursor Based Update

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

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

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

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

Update Statement Which Updates User IDs In One Table With User IDs In Another Table?

Jun 17, 2009

I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.

View 4 Replies View Related

SQL & PL/SQL :: Update Statement (lag)

Aug 10, 2011

I have a table zTest with several products:

create table zTEST
( PRODUCT number,
ID number,
Flag number,
FLAG_L1 number );

[Code]...

The field FLAG_L1 is the field FLAG with a Lag 1 (order by ID, partition by PRODUCT). I want to write the field FLAG_L1 in an update statement, how can I do this?

View 7 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

UPDATE Statement Tuning

Dec 6, 2011

I have a below UPDATE STATEMENT and it is taking more than 1hr to complete. tune this to complete as quick as possible.

UPDATE A_TABLE A
SET FLG= 1
WHERE EXISTS (SELECT ID FROM B_TABLE B where A.ID=B.ID);

Count of the table A_TABLE --- 16,331,441
Count of the table B_TABLE --- 1,271,903

We have index on B.ID and A.ID

View 3 Replies View Related

SQL & PL/SQL :: Can A Procedure Be Used In Update Statement

Aug 2, 2011

PROCEDURE split_name (
full_name IN VARCHAR2,
name1 OUT VARCHAR2,
name2 OUT VARCHAR2,
name3 OUT VARCHAR2,
name4 OUT VARCHAR2,
name5 OUT VARCHAR2
)

the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.

View 4 Replies View Related

SQL & PL/SQL :: Get Old Value Of Column In Update Statement

Sep 17, 2007

While updating a table, is it possible to get the old value of a column. I need to get it inside a PLSQL block and not in a trigger.for example :-

DECLARE
l_amountNUMBER(5) := 0;
BEGIN
UPDATECustTransactions
SETamount = 150
WHEREcust_id = 5412
RETURNING amount INTO l_amount;
DBMS_OUTPUT.PUT_LINE ( 'l_amount= ' || l_amount );
END;

By using the RETURNING clause i am getting the new updated value. I need the old value for some processing. Do i have to explicitly query it before the update stmt? Pls reply.

View 6 Replies View Related

PL/SQL :: Index Ignored While Using Update Statement?

Nov 26, 2012

I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.

The statement is very simple:
update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2;
ITEM_MASTER has unique, primary key index on SMARTPART_NUM

When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768

SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ANALYZED
------------------------------ ------------ ----------- --------------- ---------
SMARTPART_NUM 198417 254 HEIGHT BALANCED 25-NOV-12

The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)

Here are parameters related to optimizer:
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE

We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.

We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.

Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.

View 10 Replies View Related

PL/SQL :: Row Locking During Update Statement?

Aug 9, 2013

Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number) 

{update tableAset columnB = case when columnB = 3 then   4  when columnB = 4 then   5   else  columnB    endwhere columnA = 6;}

Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.

View 4 Replies View Related

Adding Column To Large Table

Aug 12, 2013

I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?

View 2 Replies View Related

SQL & PL/SQL :: Splitting Large Table Output

Sep 20, 2012

I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:

1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?

2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.

I'm trying to get the first block of the IF working first, so the rest is just placeholders.

DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....

View 12 Replies View Related

How To Get Fast Output From Large Table

Jan 25, 2013

i have three tables and all of these tables have around 30L records.

Using join i am retrieving records from these tables but it is taking much more time to get output.

Partition can improve performance?

View 7 Replies View Related

Like Statement With Multiple Values

Dec 1, 2009

I have a field that may look like this:

AS01 AB CD EF

I want to check to see if that string contains any of the values in:

select code from codes_table;

For example, if

select code from codes_table;

returns:
code
-----
AB
LM
NO
PQ

then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:

if the input string
contains any of the codes
in the field 'code' from table 'codes_table'
then
'OK'
otherwise
'No good!'

View 3 Replies View Related

SQL & PL/SQL :: Trying To Get Two Values From Case Statement

Jul 10, 2013

In the following query WHEN

supplier_name = 'IBM' and supplier_type = 'Hardware' THEN

only 'North office' is being retrieved but how to get 'South office' also along with it.Is there a way to write multiple conditions in THEN clause of case stmt something like THEN 'North office' AND 'South office'.

create table suppliers_temp(supplier_id number(5),supplier_name varchar2(10),supplier_type varchar2(10));
table suppliers_temp created.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(1,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(2,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(3,'TCS','Software');
1 rows inserted.
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Update Statement By Joining 2 Tables

Mar 24, 2012

I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values

NEW_NAME OLD_NAME
DRUG10 DRUG1

I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:

DRUG1,DRUG2,DRUG3,DRUG4,DRUG5

Each DRUG has multiple rows associated with it.

I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.

However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.

DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows

(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))

However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.

UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))

This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.

View 2 Replies View Related

Forms :: Update Statement On Button?

Apr 16, 2010

I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.

View 5 Replies View Related







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