SQL & PL/SQL :: Update Gender To Male For 4 Smallest Custid In Database
Jan 20, 2012
I want to use a single query to update gender to male for the 4 smallest custid in my database.I tried these but got errors..
UPDATE (SELECT * FROM customer_0908288 ORDER BY custid ASC WHERE ROWNUM <= 4)
set gender ='M' WHERE ROWNUM <=4;
error is: missing right parenthesis
WITH summary AS (SELECT custid FROM customer_0908288 WHERE ROWNUM <=4 ORDER BY custid)
UPDATE summary SET gender = 'M';
error is: missing SELECT keyword
View 12 Replies
ADVERTISEMENT
Mar 27, 2010
I have a table with 1 million rows,all rows are based on the gender male and female.
So that i have done two partitions based on the gender Male and Female.
After that i performed the query
SELECT COUNT(*) FROM student WHERE gender='MALE';
In that case performance is improved,because oracle scans a single partition instead of scanning entire table.
Suppose i have partitioned according to the date column and i performed the query
SELECT COUNT(*) FROM student WHERE gender='MALE';
In that case performance will be improved or not
View 3 Replies
View Related
Feb 8, 2012
I have a table like follows
Name Gender
-------------
Arun M
Anitha F
Bala M
Banu F
I need the output as follows
Male Female
-----------------
Arun Anitha
Bala Banu
What are the ways can we generate the above query
View 14 Replies
View Related
Oct 13, 2012
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
View 2 Replies
View Related
Jun 26, 2013
I want to replace a word with another word in all tables data with varchar2 datatype that I have in my database .Is there a solution to update data at once instead of update all column data Separately.
View 8 Replies
View Related
Sep 22, 2010
I have data as below:-
select qualifier from nv2_audit_log where qualifier like '%NXPRI%NXUPM%';
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
The question is how could i update all the qualifier from /acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910 to /acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910 where qualifier like '%NXPRI%NXUPM%' ?
Actually i want to change the NXPRI to be NXUPM only for all record with qualifier like '%NXPRI%NXUPM%'
The output must be like this:-
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
View 2 Replies
View Related
Jan 31, 2011
I can not update and delete row from database
View -1 Replies
View Related
Mar 10, 2011
I am having trouble changing a non-displayed field when a displayed field is changed.
When a user wants to un-assign a territory they want to just null the ID field (non-database item). I then need to null the PIDM field(not visible, database item) for that one record.
I tried this, but I end up putting null on all the records instead of just the one record, which ends up un-assigning all the territories not just the one.
Is there a way to assign the null to the PIDM field associated to the ID field of that particular record.
View 6 Replies
View Related
Mar 9, 2010
I am preparing a query form based on one view.it will return the result set based on parameters passed.What i need is i will add 3 non database item in entry mode along with result set.Once the user inputs these 3 fields in non database fields , it should update the relevant records mapped to it in database table. How to do this.
View 19 Replies
View Related
May 17, 2013
I have to check some parameter values for an OID tuning I do not have sys access, I have only schema user access
Now how do I see the values of SGA_TARGET, db_cache_size etc parameter
I manage to see these var in init.ora file under /dbs folder but there are 2-3 spfile also having containing these attributes, not sure which reflect the actual.
Also can I alter also these value via schema user or do I need SYS access?
View 17 Replies
View Related
Feb 7, 2013
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies
View Related
Jun 14, 2013
Was going through this and found that
“AUDIT DELETE ANY TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE<username> BY ACCESS”
enable audit for delete and updates for given username/schema. I want to enable auditing on delete and update on my entire database.
Why? And have we tested it in our any of existing setup? I am thinking of “Trigger after delete” but again this logic gets struck at individual tables. It do not work simply once and all for complete database/all users/all schemas
View 15 Replies
View Related
Apr 16, 2013
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 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
Sep 14, 2012
Application Express ver. 4.1
I have created a form. Some of the elements are hidden such as updated_by and update_date. I have provided the default values for these attributes as pl/sql expression in the "default section" (Application->page->edit page item). I am using the Process Row process that is automatically created when a form is created.
The problem is when I click the update button, the updated_by and update_date is passed as null values which throws an error from the database.
View 2 Replies
View Related
Dec 22, 2010
The following control file updates multiple rows in database table.
LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(Data LOBFILE(CONSTANT cadd_pass.xml) terminated by eof
There are 21 lines in the xml. So 21 rows are updated in table.update only one row?
View 9 Replies
View Related
Sep 25, 2012
I am experimenting with the 4.2 version of Apex at URL.....
I have created a tabular form (based on a view with an instead of trigger) and have restricted this form to "Update only" mode. Whenever I modify a field value and submit the change I get the error "Current version of data in database has changed since user initiated update process".
View 6 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
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
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
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
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 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
Mar 21, 2011
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
View 11 Replies
View Related
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
View 13 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
Aug 14, 2010
i have two databases and created the link between them. I can easily query the data but when i need to update my local records from the remote its showing an error
SQL> update laptop set name =
2 (select name from laptop@ora_link1 where id between 2 and 4)
3 where id between 2 and 4;
(select name from laptop@ora_link1 where id between 2 and 4)
*
ERROR at line 2: ORA-01427: single-row subquery returns more than one row
select multiple rows from the remote db and update them in the local db.
View 2 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
Oct 5, 2010
I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.
[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...
View 3 Replies
View Related
Oct 26, 2010
I have to update a table by getting values from two other tables. While doing that the inner query returns more than one value. I am not sure how to implement the logic without returning more than one row in sub query.
My query:
update buf_office_str o
set o.manager_ident =
(select sp.ident
from se2_r_src_sourceperson sp ,
(select distinct director_name, team_name from buf_sales_dump )t
where SP.SRCNAME = upper(substr(t.director_name,instr(t.director_name,' ')+1,length(t.director_name))||', '||substr(t.director_name,1,instr(t.director_name,' ')-1 ) )
and o.office_descr = t.team_name
)
Basically the query gets the manager id from sp table where sp.srcname = t.team-name.
The office_descr should be equal to the team_name.
This is the logic I am working towards:
For each office, i get the office_descr and get corresponding team_name. Match the team's director_name (from table t) with the sp.name and return the employee's id (sp.ident) for that office_descr. I need to update all 50 offices with corresponding managerid for that office in buf_office_str table.
Is it possible to get done in one update? Or, does this need a plsql proc to do this logic.
View 3 Replies
View Related
