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


ADVERTISEMENT

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

SQL & PL/SQL :: Displaying Record When All Columns Have Matching Records?

Jul 3, 2012

I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed

The following is the example

WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all

[code].....

In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.

View 8 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like

CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234

View 11 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

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

ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?

Oct 1, 2012

I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.

HERE

select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.

View 12 Replies View Related

SQL & PL/SQL :: Update Number Of Rows In Three Columns In Table?

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

SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table

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

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

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

Forms :: INSERT Record - Update Columns Of Table

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

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

Any 200 Matching Records From A Table?

Aug 10, 2011

I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".

which is the best option to make most efficient search --

1) rownum < 201
2) row_num()
3) rank/denserank etc

View 3 Replies View Related

SQL & PL/SQL :: How To Match Two Words Matching From Two Table

Aug 19, 2013

find two words matching from two different tables.

Example:-

table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis

output:-

john dev || john dev
kab leva || kab leva

View 9 Replies View Related

SQL & PL/SQL :: Unique Identifiers Matching Datatype In Oracle?

Jan 19, 2011

what is the matching datatype of unique identifier in SQL to Oracle ques regd SSIS when I map a unique identifier in SQL to Oracle via SSIS, I get curly braces at both ends in the end result.

1B66FE97-A9CA-4E0D-9593-00046E2AF7E1 - i/p(SQL Server)
{1B66FE97-A9CA-4E0D-9593-00046E2AF7E1} - end result(Oracle)

In SSIS, I convert the unique identifier to string data type.

View 1 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

PL/SQL :: Getting Error While Inserting Columns In Oracle Table?

Sep 3, 2013

I was trying to insert a row into Oracle database consisting 20 columns through IBM Web Experience Factory. I am getting "Array index out of range: 20" error message. To my knowledge insert is working fine till 7 columns. Is it a driver problem or do i need to install any fix pack?

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

Oracle Trigger - Update Same Table?

Mar 30, 2010

I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.

So far I have:

DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;

[Code]....

I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.

The procedure IsCDCUser just checks it should be updating for this user.

The problem I get is the following error:

Bind Variable "NEW" is NOT DECLARED

View 3 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

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

SQL & PL/SQL :: Update When ID Column Is Matched / But One Of Others Columns Not

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

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 :: Update Remaining Empty Columns

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

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

Forms :: Update Table Data Via Oracle 10g

Jul 7, 2011

I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.

user wants the following layout:student Advisor
ID# name degree major Concentration ID# name
The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).

The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.

View 6 Replies View Related

SQL & PL/SQL :: Is There Multi-table Update Facility In Oracle

Nov 3, 2011

Just like Multi-table insert(using INSERT ALL) is there multi-table Update facility in Oracle? In which version?

View 1 Replies View Related

Application Express :: Oracle APEX_ITEM For Non Database Table Columns - User Entered

Aug 21, 2012

how to use APEX_ITEM.text for where the source for this comes from user input values and not for an underlying table.I have a report where three columns are coming from an actual table (one being a checkbox for selection) but then also have two additional fields on the screen that are not from a table but placeholder fields for user's to enter data.

For example, my query looks like this:

select apex_item.checkbox2(10,id),
name,
telephone,
apex_item.text(20,NULL) as "Date Contacted",
apex_item.textarea(30,NULL,5,80) as "Comment"
from my_table

I am not seeing any values for the two user fields when entering values and doing a view source?

View 2 Replies View Related

SQL & PL/SQL :: Bulk Collect - Update Two Columns Without Any Filtration

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

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

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

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







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