PL/SQL :: Update Table Column Using Complex Sequence
Feb 6, 2013
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
View 5 Replies
ADVERTISEMENT
Mar 25, 2013
My table have duplicate records. I want to impose a primary key on that table. For that I have to replace duplicate values of a column with next value of a sequence.
View 2 Replies
View Related
Aug 21, 2013
1)I have created a complex view, and created an instead of trigger on this view
example:
create or replace trigger tr_x instead of before update on test_view_name for each row
-------
2) I have created an update policy on this view
DBMS_RLS.ADD_POLICY (
object_schema := schema_name,
object_name := name of the view,
policy_name := ploicy name,
function_schema := func schema name,
policy_function := pkg_test.fn_get_where,
statement_types := 'UPDATE',
update_check := TRUE,
policy_type := dbms_rls.dynamic);
3) function pkg_test.fn_get_where, which is used in the policy function always return 1 = 2, so that update should fail.
4) Now I will issue an update statement on the view test_view_name
update test_view_name set test_col = 1;
but still it updates the records, though update policy returns the where cluse 1 = 2
same where clause for select policy works perfectly fine.
View 4 Replies
View Related
May 6, 2010
I want to create a table, whose first column , patient_id, should be a sequence.
View 9 Replies
View Related
Dec 8, 2012
I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
View 2 Replies
View Related
Dec 8, 2012
I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04.
I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"
View 4 Replies
View Related
Aug 23, 2012
I am having the emp table :
empno enmae job
1111 ramu S.E
7658 VENU S.S.E
8280 GETA TRAINEE
alter table emp add sno number
for that sno i have to generate 1,2,3,...
View 4 Replies
View Related
Oct 1, 2011
I found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
View 7 Replies
View Related
May 11, 2012
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) ?
View 7 Replies
View Related
Oct 22, 2010
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
View 2 Replies
View Related
Feb 21, 2011
How to update a CLOB column in a table, suppose the string is greater than 4000 size.
View 7 Replies
View Related
Feb 21, 2012
CREATE 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 )
View 13 Replies
View Related
May 26, 2011
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 .
View 26 Replies
View Related
Apr 14, 2012
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
View 13 Replies
View Related
Sep 2, 2011
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.
View 2 Replies
View Related
Jul 31, 2013
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]....
View 10 Replies
View Related
Apr 1, 2012
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.
View 1 Replies
View Related
Apr 10, 2013
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
View 5 Replies
View Related
Nov 23, 2011
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]....
View 8 Replies
View Related
May 25, 2012
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 ;
View 1 Replies
View Related
Apr 17, 2013
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
View 6 Replies
View Related
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
Sep 30, 2013
, im having this trouble to update some records, the problem is i have I have this table with some transactions and each row have a cd_cli (client code), dt_ven (date of the transaction) and a product id (cd_prod) the trouble is, i insert a row for each product in this table, then i have like this
110001YURI AGUIRREWALLMA400V44-B469,0019/9/2013210001YURI AGUIRRESU-7N-B78,0019/9/2013310001YURI AGUIRREWALLSF35V22-S78,5719/9/2013410003WILLIANWALLSA470V22-BS449,002/9/2013 but i need to update the CD_TRANS for each row with the same number like all the YURI AGUIRRE recode must have like CD_TRANS 1, and the WILLIANs CD_TRANS must be 2 and the next must be cd_trans 3, but I've tried to update the table for a lot of ways but i don't have success trying to do this, the last thing i had tried was
MERGE INTO TVEN_TEMP a
USING (SELECT CD_CLI, DENSE_RANK () OVER (ORDER BY DT_VEN) new_seq_num
FROM TVEN_TEMP
WHERE CD_TRANS IS NULL) b
ON (a.CD_CLI = b.CD_CLI)
[code]....
View 8 Replies
View Related
Sep 17, 2013
I'm currently facing a non-critical situacion while trying to update a table. Here's the PL/SQL Code. It's a pretty straight-forward script, It just update one single column of the table. The only problem is that I have to update over 15 milions records. When I use the for update cursor, I put inside the loop a counter, when it reaches 1000 records, then commit, else keep counting.
The block throws the exception ORA-01002: fetch out of sequence.
I've tried to do some reserch on Google, buy it only says it is an out of secuence fetch caused by a commit inside a for update cursor.
My question is. Is there any risk by putting the commit under the end loop, I mean by doing this I'd be updating over 15.000.000 records at the same time. Will I have any issue with the rollback segment ?
Here's the code as along with the Create Table and Insert statements.
CREATE TABLE TEST_1
(
TEST_ID NUMBER PRIMARY KEY,
IS_LOCKED NUMBER NOT NULL
);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(1, 0);
[Code]....
View 12 Replies
View Related
May 21, 2013
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]....
View 15 Replies
View Related
Dec 6, 2010
I need to write a procedure to be able to reuse it to clean up subcategory sequence. Here is a problem: two tables: Category and subcategory:
create table Category
(category_id number,
Name varchar2(20))
/
create table Sub_Category
(sub_category_id number,
category_id number,
sequence number)
/
In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.
So ideal world is :
Insert into Category values (123 ,'Category1');
Insert into Category values (234 ,'Category2');
Insert into Category values (345 ,'Category3');
Insert into Category values (456 ,'Category4');
Insert into Category values (567 ,'Category5');
Insert into Sub_Category values (1,123,1);
Insert into Sub_Category values (2,123,2);
Insert into Sub_Category values (3,123,3);
But I've also bad rows like this:
Insert into Sub_Category values (4,234,1);
Insert into Sub_Category values (5,234,1);
Insert into Sub_Category values (6,234,1);
Insert into Sub_Category values (7,345,1);
Insert into Sub_Category values (8,345,1);
[code].....
Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.
I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:
CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
IS
CURSOR c1
IS
select category_ID from category where ...;
BEGIN
FOR a IN c1 LOOP
UPDATE_SUBCATEGORY(a.Category_id);
COMMIT;
END LOOP;
END;
/
And the actual procedure updating subcategory:
CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
BEGIN
............
END;
/
write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?
View 17 Replies
View Related
May 14, 2010
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?
View 4 Replies
View Related
Jul 21, 2011
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
/
View 9 Replies
View Related
May 29, 2012
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;
/
View 3 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
