SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?
Feb 16, 2011
The requirement I have is :
I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)
update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))
It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.
View 1 Replies
ADVERTISEMENT
Aug 20, 2013
create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)
[code]....
Can I write a single update statement to update filename column replacing "_tst" with "_check"?
View 1 Replies
View Related
Jul 24, 2009
Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.
Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.
Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.
MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.
Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row
UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))
Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.
View 1 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
Oct 17, 2012
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
View 1 Replies
View Related
Oct 17, 2012
I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
View 9 Replies
View Related
Mar 24, 2010
I have records:
owner company
A X
A Y
A Z
B X
B Y
C X
owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0
How do I write the SQL?
View 2 Replies
View Related
Feb 28, 2012
CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/
[Code]...
I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.
If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.
View 3 Replies
View Related
Mar 22, 2013
writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:
Original table data:
Col1 Col2 Col3 Col4
Row1 a1 a,b,c 01 ON
Row2 b1 d,e,f 02 OFF
Row3 c1 g,h 03 ON
I want the above table to be transformed into below:
Col1 Col2 Col3 Col4
Row1 a1 a 01 ON
Row1 a1 b 01 ON
Row1 a1 C 01 ON
Row2 b1 d 02 OFF
Row2 b1 e 02 OFF
Row2 b1 f 02 OFF
Row3 c1 g 03 ON
Row3 c1 h 03 ON
View 2 Replies
View Related
Jun 25, 2012
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO
View 1 Replies
View Related
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 Replies
View Related
Dec 1, 2009
I have a field that may look like this:
AS01 AB CD EF
I want to check to see if that string contains any of the values in:
select code from codes_table;
For example, if
select code from codes_table;
returns:
code
-----
AB
LM
NO
PQ
then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:
if the input string
contains any of the codes
in the field 'code' from table 'codes_table'
then
'OK'
otherwise
'No good!'
View 3 Replies
View Related
Jul 27, 2012
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.
View 10 Replies
View Related
Nov 28, 2012
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
View 3 Replies
View Related
Nov 26, 2010
I have a table like this:
ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................
and I would like to have a row for each year between ini_date and end_date.
ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010
View 2 Replies
View Related
Feb 19, 2010
I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:
SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)
SQL> select * from test1 order by id;
ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8
The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;
ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,
what I want is : null value should not come and duplicate value should not come (Value4 in output above)
View 11 Replies
View Related
Dec 31, 2012
Version oracle 11g
Is there a way to avoid multiple UNION statements and get statitc values in single SQL?
select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that
View 4 Replies
View Related
Oct 4, 2012
I have a user requirement where they want to be able to select multiple values for a single parameter that will act as a filter for a report. The possible values for a parameter can come from almost any master data Oracle eBusiness table. I don't want to just present the user with a LOV where they search down a list selecting what they want. That wouldn't be very practical for selecting part numbers from 1000's of rows. I also want the user to easily see what values they have selected. I thought about providing a button that would navigate to a multi-row form where they could record their values and use those values in the where clause of the SQL for the report, but that is a somewhat complex solution I have never tried before. how to do this. I'm working in the latest version 4 of APEX.
View 1 Replies
View Related
Jun 3, 2010
I need to create a SQL insert statement with mutipleValues and an select statement I am trying this it works -
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
values
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,'TEST1', 'NITA')
This statement does not work (with or without keyword) Is there any alternate syntax
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,(select last_name, first_name from uwa_crew_ids where guid = '8795EAAFBE694F8EE0440003BA2AEC90' ))
View 3 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Jun 1, 2010
How to hold the multiple rows values using array? And I have to pass this values to some other procedure.
Ex: SQL> select ename from emp;
ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil
View 7 Replies
View Related
May 30, 2013
I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:
SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....
but for some reason regexp_replace does not seem to work with clob and I get:
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Is it possible to eliminate duplicates in the model before the concatenation?
View 6 Replies
View Related
Aug 21, 2013
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as (
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all
[code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all
[code]...........
View 12 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
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
May 4, 2010
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
View 4 Replies
View Related
Jun 14, 2012
I've a table with fields:
create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,
[Code]....
View 7 Replies
View Related
Nov 13, 2013
I am trying to do this:
UPDATE projectdetails SET projectdetails.projectname=(SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname);
but it returns with the error: single row subquery returns more than 1 row
I tried in the same PROJECTDETAILS table with another table A3, it works fine. but not for the PROJECT table.
Below is the project table (i have 2055 rows)
projectidprojectname1NEWEST2PARK WEST3THE VISION4PEOPLE'S PARK CENTRE5REFLECTIONS AT KEPPEL BAY6THE CLIFT7CLEMENTIWOODS CONDOMINIUM8HUNDRED TREES9PASIR VIEW PARK10CARABELLE11PARC IMPERIAL
below is the projectdetails table:
ProjectnameCORALS AT KEPPEL BAY20247502294883Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY278688018901475Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY15250502180700Strata101 to 05Jun-1399 yrs lease commencing from 20071ECHELON216298016471313Strata121 to 25Jun-1399 yrs lease commencing from 20121WHITEHAVEN135600012851055Strata101 to 05Jun-13Freehold1
View 14 Replies
View Related
Nov 8, 2011
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus
[code]....
View 5 Replies
View Related
