I have a table like:
0035987850 P
0035987851 P
0035987852 P
I would like to update removing 00 in the first column. So after update to have:
35987850 P
35987851 P
35987852 P
I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
I have a table ITEM as under;
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19I
4KABAN.NM.12.21564101SU23I
4KABAN.NM.24,5.22491015SR08I
4KABAN.NM.24,5.23181038SR22I
4KABAN.NM.24,5.23491015SR08I
4KABAN.NM.24,5.24121161SR26I
how to update the filed from NAME to NEW_NAME, I need following output.
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19ISU19
4KABAN.NM.12.21564101SU23ISU23
4KABAN.NM.24,5.22491015SR08ISR08
4KABAN.NM.24,5.23181038SR22ISR22
4KABAN.NM.24,5.23491015SR08ISR08
4KABAN.NM.24,5.24121161SR26ISR26
How to update a CLOB column in a table, suppose the string is greater than 4000 size.
View 7 Replies View RelatedCREATE TABLE TEST
(
NAME VARCHAR2(1000 BYTE),
ID NUMBER
)
[Code]...
I want the update the TEST table with TEST1 based on id column,i tried as like.it's not working..
UPDATE test t1
SET t1.ID = ( select ID from test1 t2
where t1.name = t2.email and rownum=1 )
i have a table in my PD database which have more than 30,000 records .some records in a column say p_code is not tagged with code like '9876543'while other records are tagged in this column with code such as '19022345678'.
Now i want to update these records with tagging 1902 with each one .
I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:
MR_No Char(13),
Mr_Date Date
My Previous data is MR_NO=APN00209085
I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command
update PR set Substr(MR_No,4,2)='00'
ERROR at line 1: ORA-00927: missing equal sign
Result I want is APN0000209085
i need a query for update..the logic is
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
I have a scenario where I need to update field in report_input table.It has bind variables in it which will be prompted while running the query.I see that QUERY_VALUE field that need to be updated has length of VARCHAR2(3000).
desc report_input
Name Null Type
----------------- -------- --------------
QUERY_NAME NOT NULL VARCHAR2(64)
QUERY_VALUE NOT NULL VARCHAR2(3000)
[code]....
I am trying to update a column based on another column in the same table (student table) and a column from another table (school table)
Code is:
update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)
I get the following error ORA - 01427 Single-row subquery returns more than one row.
I want this trigger to automatically update the num_songs_in_album column in the album table whenever rows are inserted or deleted into the Track table.
SQL> CREATE OR REPLACE TRIGGER update_num_songs_in_album
2 BEFORE INSERT OR DELETE ON Track
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
[code]....
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
I use a sequence to generate an alphanumeric code and I need to update all rows of a table with that sequence. The sequence is created with this code:
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I have tried many things - most recently
Update t3_temp_table
SET mergecode =
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I get missing expression error.
It works fine if I use a straight s.nextval
We had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
Edit/Delete Message
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.
To remove fragmentation which is the best method.
First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.
Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
[code]...
I am facing some challenge while running update query on newly added column in existing table.
Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24
Problem faced & My analysis
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[Code]....
We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.
However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?
I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?
create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4
eg: - 23115645
after masking should be - xxxx5645,
create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit
[Code].....
y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx
I tried the above update, but using substr, i am able to get this if x has 9 characters
not sure how to achieve this if x has 7, or 4 characters
Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-
This is input data in hex value:-
STARTTIME : 080b1317021a
This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226
My problem is how could i convert hex to decimal in oracle? Below are my coding:-
case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....
I am using the oracle 10g, I am trying to write the a sql query by which in a below mention patterns I can replace the digits after a string 'CVV' with X. The no of X will be equal to the no of digits after CVV
1. BTA CVV 8810
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVV498
3. C***CVV VI 569***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV 063/dffddf:dfdfdfd
5. (T) CVV 4671
6. MS-ACEEML/CVV
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES
AFTER a change above data should look like as mentioned below
1. BTA CVV XXXX
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVVXXX
3. C***CVV VI XXX***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV XXX/dffddf:dfdfdfd
5. (T) CVV XXX
6. MS-ACEEML/CVV -- no change for this
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES --- -- no change for this
Initially I tried to find the position of CVV in any string and then from that position take the digit and replace them with X. I tried the below code which is not complete
select REGEXP_INSTR('BTA CVV XXXX','CVV',1,1) from dual;
Is there a way to find out the ending digits in a string?
Examples of input and outputs:
'ABC123' -> 123
'ABC' -> NULL
'123ABC' -> NULL
'123ABC456' -> 456
'123ABC456QP98' -> 98
I have figured a way to do it by doing Reverse and re-reversing using: reverse(regexp_substr(reverse(p_string), '[ [:digit:]]*'))
But I'm sure there is a way to do it backwards without using reverse function, which I am not able to properly put in the syntax.
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?
I have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
How Do I Create An Interactive Program That Will Add Numbers Between Two Given Digits Using Sql. For Example:
Enter Start Num: 0
Enter End Num: 15
Then It Does 1+2+3+...........+14
How to write SQL query for selecting all the values (numbers) in the table which has 3 digit after decimal point.
For example say A is the table it has following values
200.24
300.456
123.22
1234.344
From this I need only
300.456
1234.344
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
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.
I am facing an issue here at my workplace, there is a report developed in report builder , the user wants to see the digits in arabic.
View 4 Replies View Related