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


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

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 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 :: Updating Multiple Rows With Single Update Statement?

Aug 20, 2013

create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)

[code]....

Can I write a single update statement to update filename column replacing "_tst" with "_check"?

View 1 Replies View Related

Performance Tuning :: Get Number Of Rows Processed While Update Statement Is Still Running

Aug 25, 2010

Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.

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

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

Oracle SQL - Update Table Value By Matching Columns

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

SQL & PL/SQL :: Using Update With A Join?

Mar 16, 2011

im trying to update a column in the employee table with the value "YES". Im getting an error message saying im missing a SET statement from this code below:

update e
SET e.review='YES'
from employee
inner join rentals r
on e.employee_id=r.employee_id
inner join job j
on e.job_id=j.job_id
where r.plate ='FY06WNT'
and j.function !='MANAGER'
and j.function !='PRESIDENT';

View 2 Replies View Related

SQL & PL/SQL :: How To Update With Join

Jul 7, 2010

drop table dev10 purge
/
drop table dev11 purge
/
drop table dev12 purge
/
create table dev10 as
select rownum c1, sys.dbms_random.string('U', 6) c2, trunc(sys.dbms_random.value(1, 7)) c3
from dual connect by rownum < 8
/

[Code]...

Now, Let us assume that, the record is

AAAAAA BBBBBB CCCCCC
DDDDDD EEEEEE FFFFFF
...
..
.

Now, we want dev12.c2 is 'FFFFFF' if dev11.c2 is 'BBBBBB', if we want to get this:

AAAAAA BBBBBB FFFFFF
DDDDDD EEEEEE FFFFFF
...
..
.

We can make this for SqlServer by coding:

UPDATE dev10
SET c3 = dev12.c1 FROM dev10 INNER JOIN dev11 ON dev11.c3 = dev10.c1 CROSS JOIN dev12
WHERE (dev11.c2 LIKE 'BBBBBB')
AND (dev12.c2 LIKE 'FFFFFF')
/
but, Oracle, what should we do new?

View 11 Replies View Related

Update With Join

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..

translate this query to work on oracle?

View 5 Replies View Related

SQL & PL/SQL :: Join Update Clause

Jun 9, 2010

I want to use join condition in update syntax.Like the following way but it doesnot work.how to fix it.

update tab_1 a
set a.qty = b.sell_qty
from tab_2 b
where a.nbr=b.nbr

View 3 Replies View Related

SQL & PL/SQL :: Update With Join Over Two Tables

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

SQL & PL/SQL :: Update In Join Condition

Feb 16, 2011

I Require to Update the Data in Join Condition. When Run the Query the Error display as ORA-00933: SQL Command Not Properly Ended.

Query:

Update a set a.Dr_Re = Nvl(b.Dr_Amt,0),
a.Cr_Re = Nvl(b.Cr_Amt,0)
FROM xxsc.xxsc_creditors_aging_brnwise a
join (Select Branch,Invoice_id,vendor_site_code,segment1,
case when (sum(nvl(dr_re,0)) - sum(nvl(cr_re,0)) > 0) then sum(nvl(dr_re,0)) - sum(nvl(cr_re,0))
Else 0 End DR_AMT,
[code]........

View 2 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related

SQL & PL/SQL :: Update Join View?

May 20, 2010

9i worked fine 11g release2 giving ora-01779

alternative sql for the following :

UPDATE /*+ BYPASS_UJVC */
(
SELECT
c.c1,
c.c2,
c.c3,

[code].....

View 5 Replies View Related

SQL Code For Update Using Join Conditions?

Jan 8, 2011

novice to SQL (Oracle 10g)

Am trying to write code for sollowing scenario:

Have 3 tables
table1 (campaignid,promoflag)
table2 (campaignid,projectid,campaigndesc)
table3 (projectid,promoflag,projectstart,projectend)

I am to update table 1 promoflag with value from promoflag in table3

Update table1
set promoflag = table3.promoflag

I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.

I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid

Have used the following without success:

Scenario 1

Update table1
SET promoflag = table3.promoflag
FROM table1
inner join table2 on table1.campaignid = table2.camapaignid
inner join table3 on table2.projectid = table3.projectID;

Error at line 1 ORA-00933: SQL command not properly ended

Scenario 2 with real table/column names

Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID;
Error at line 2
ORA-00933: SQL command not properly ended

It appears to get block with the 'FROM' statement (was underlined in red)

View 1 Replies View Related

Update With Join Syntax In Oracle

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?

View 4 Replies View Related

SQL & PL/SQL :: Update Query Based On Join Condition

Jun 16, 2011

I have two tables. By joining these two tables, I need to update a field in table1.

UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')

I keep getting error 'table1.lastname' is invalid identifier.

I can't understand the error message. I made sure that the fields exist.

View 5 Replies View Related

Forms :: Update Record In Join Condition Block?

Jun 13, 2012

i want to update record that is fetched based on join condition on form

1. made a block manually :::: EMPSAL
2. Query DATA SOURCE NAME :::: EMP a, Sal b
3. Where Clause :::: a.empid = b.empid
4. DML DATA Target Type :::: Table
5. DML DATA Target Name :::: EMP a, Sal b
6. All Columns are marked a.empid, a.empname, b.sal, b.date etc

It does not allow me to update record.

View 1 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error

ORA-00933: SQL command not properly ended.

UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

[code]....

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

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

SQL & PL/SQL :: Update All Rows At Once

Aug 28, 2011

I want to update salary column of emp table in a way that every value of salary column be increased by 1000, is this possible I can do this one statement only??

(Just FYI-

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

)

[Code].....

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







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