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.
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"
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"
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.
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 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 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]....
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
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.
, 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)
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);
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;
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
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 ?
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 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; /
