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
ADVERTISEMENT
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 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
May 25, 2010
Is it possible to have a dummy row when the record inside the IN condition doesnt matches?
E.g. :
select * from table1 where record in ('A','B','C');
Here record 'A' is not in table.
Expected output:
record name
-------------------
Adummy row
BRajiv
cRobin
View 6 Replies
View Related
Mar 17, 2010
My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.
create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...
I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.
select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....
View 20 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
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
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
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
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
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
Dec 28, 2010
I was wondering how can I do below statement in oracle
update table1 t1 set t1.column1 = t2.column2
from table2 t2 inner join table3 t3 on t3.column3=t2.column4
where t3.column5 is null
I tried read up merge and update, but not really understand how the syntax works in oracle.
View 2 Replies
View Related
Aug 10, 2010
I just ponder how to write SQL or PLSQ if I'd like to get more columns into the one column.
For example:
I have table T1 where are 10 records. One columns TEXT is VARCHAR2. And I need to get by SQL/PLSQL one record with the new column VARCHAR2 where will be written 3 records as I wrote.
select TEXT from T1:
result: aa
bb
cc
And the desire result should lool like:
aa bb cc
how to quickly do it?
View 2 Replies
View Related
Nov 2, 2012
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
View 2 Replies
View Related
Nov 22, 2010
I am able to get a list of all the duplicate location_code values.
select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.
select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code
View 4 Replies
View Related
Jul 8, 2010
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies
View Related
Jun 5, 2013
I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:
select column1 from tablename where column2 = 'abc'?
Is there a way in a query/update/insert to refer to a column by column number rather than column name?
declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;
[code]....
View 4 Replies
View Related
Apr 30, 2010
writing SQL, where I need to invert the column data into columns. My data in DB is as below.
MONTHNAMEVALUE
Jan-10M1 5
Jan-10M2 8
Jan-10M3 9
Feb-10M1 4
Feb-10M2 6
Feb-10M3 2
Feb-10M4 10
Mar-10M1 21
Mar-10M2 6
Required output for reporting will be like:
MONTHM1M2M3M4
Jan-10589
Feb-1046210
Mar-10216
How can I achieve this using SQL.[ORACLE 10g] ?
I can make use of Oracle BI server also.
View 2 Replies
View Related
Dec 22, 2011
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
View 5 Replies
View Related
Sep 23, 2013
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies
View Related
Jul 31, 2012
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
View 3 Replies
View Related
Nov 16, 2012
I want the output from two tables with rows to columns and generate dynamic basing on the columns.
For example: Table A (a1) , Table B(a1,b1)
Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c
o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c
Columns should be generated based on the second table second column.
View 18 Replies
View Related
Mar 15, 2013
I have table t1 and (a1,a2,a3) are columns
I have this query
select a1 from t1
union
select a2 from t1
union
select a2 from t1
It's alternative is unpivot, but internally it calls table multiple times. I want a way, where I want the same output but calling the table only once.
View 4 Replies
View Related
