can we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form
2)Reservation Form
3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
I have a result of Query like this:
Col1 Col2 Col3
T1 15 20
T1 18 19
T1 5 20
T2 15 20
T2 18 19
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.
View 17 Replies View Relatedexport a subset of the data only from one database to another. Both on AIX.
Source/testdatabase 11.2.0.3 (non-partitioned tables)
Target productiion database 11.2.0.3 (partioned tables)
Tables same column names but diffrenet index structures and traget one to be partitioned hence only want to import the content Each table on source datbaase hascolumn seq number and only want to extract the last few months of data.
TABLES:table1,table2...
DUMPFILE=dump_dir
CONTENT=data_only
QUERY= table1:"WHERE seq_num >100 "want to use expdp but not sure about how to ensure all tables have the WHERE seq_num >100 condition, if leave table1: out and just have
QUERY= "WHERE seq_num >100 " will this condition be applied to all tables which is what we want.
I'm assuming also can use impdp CONTENT=data_only?
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........
View 6 Replies View RelatedI have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.
what i am looking for is something like
alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null
find the logic to query data based on a condition like..I am having a table dummy
USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.
stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.
where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')
I have a flat file as source wherein I am getting values like
Comp_id, Comp_name, ISIN, column_name, column_value
The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.
E.g. of Feed File -
Comp_id, Comp_name, column_name, column_value
1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023
Target table structure
Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode
I need to insert the above data to a table by selecting data from above scenario.
I want to load single column data into multiple columns.
View 3 Replies View RelatedActually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
I want to delete the records grater than two weeks old.From Saturday to Friday we are considering as Week.
P_ID CREATED_DT
105 28/AUG/2013
106 29/AUG/2013
107 30/AUG/2013
108 31/AUG/2013
109 01/SEP/2013
110 02/SEP/2013
[code]....
From 28/AUG/2013 to 30/AUG/2013 records has to be deleted becuase thease records are two weeks old.
From 31/AUG/2013 to 06/SEP/2013 records belongs to one week and from 07/SEP/2013 to 13/SEP/2013 belongs to another week.Today is 12/SEP/2013 so from 06/SEP/2013 to 12/SEP/2013 should be existed in the table.Once date changed to 13/SEP/2013 then from 31/AUG/2013 to 06/SEP/2013 records has to be deleted.
I am posting table and insert scripts.
CREATE TABLE process_data(p_id NUMBER,CREATED_DT DATE);
INSERT INTO process_data VALUES(TO_DATE('28/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('29/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('30/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('31/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('01/SEP/2013','DD/MON/YYYY');
[code]....
Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns
1.Dept
2.sales_amt
3.status
There are 3 different flag for status A,B & C.I want display data in the following format in five columns.
Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30
total 40 110 50
But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.
For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is
select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept
Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like
Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30
So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.
I have data some thing like this.
NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 100
2 B BGL 200
1 A HYD 200
1 A HYD 150
I want to delete duplicate records (group by no,name,loc) but only max(qty) record should be retained. So I need output like this.
NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 200
2 B BGL 200
I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
I have a table structure and data as below.
create table production
(
IPC VARCHAR2(200),
PRODUCTIONDATE VARCHAR2(200) ,
QUANTITY VARCHAR2(2000),
PRODUCTIONCODE VARCHAR2(2000),
MOULDQUANTITY VARCHAR2(2000));
[Code].....
Now here i want to fetch data having condition as
PRODUCTIONDATE >= Monday of current week
so i would skip only first two rows and will have to get all rows.
I tried using below condition but it would give not give data for 2013 values.
to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))
how to insert data in a table by deleting previous entered data and only inserting current data like:
CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);
[code]....
I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:
INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);
it must show aaa,8 and aaa,9 bt not the previous values.
NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
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 ;
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country
Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well
2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query
pls guide me with some query example
I have a table with around 650,000,000 rows and we need to delete about 60,000,000 rows at the end every month and same amount of rows accumulate throughout the month. The deletion usually takes overnight to delete. We are using 10r2 in IBM AIX. The procedure we are using to delete is:
declare
ln_count number:=0;
begin
for i in (select rowid from table1 where some_id<2012090000)
loop
delete from table1
[code]...
When this procedure is started I mostly see that the session is busy in user i/o wait for db sequencial file read. Will using cursor instead will give better results.
I need to create a stored procedure in Oracle 9i which will automatically delete data one by one from a particular table and then by means of same procedure insert record one by one in same table.
View 6 Replies View Relatedhow to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?
View 6 Replies View Relatedhow i can chk & delete duplicate rows from a table
View 3 Replies View Related
