Subquery Returning Multiple Rows And Update
Mar 3, 2010
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A
ID, Value
-- -----
1 A
1 A
2 B
Table B
ID, Value
-- -----
1 Animal
2 Box
Table A (modified)
ID, Value, Name
1 A Animal
1 A Animal
2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
View 10 Replies
ADVERTISEMENT
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
Mar 1, 2011
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;
View 3 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
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
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
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
Oct 18, 2012
Can we use a subquery after the update keyword??
For example
UPDATE ( Select col1 from test)
set col1='x';
In short can we replace the table name after an update and use a select statement instead??
View 3 Replies
View Related
May 22, 2012
I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.
I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.
PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).
UPDATE PS_NTSR_GF_STUFILE a
SET a.NTSR_GF_ENRL_STAT = nvl((
SELECT b.GBSA_SUB1
FROM PS_GBSA_DTL b, PS_ACAD_STDNG_ACTN c
WHERE c.ACAD_STNDNG_STAT = b.GBSA_VALUE
[code]....
View 1 Replies
View Related
Apr 26, 2013
Should the following code work? I feel like I have done this before, but I'm getting a "must be a subquery" error now.
update TABLE1
set (FIELD1, FIELD2) =
values(select 'blah','blah' from DUAL)
where PK_FIELD=12345;
If this is invalid, is there another way to hardcode a series of values like this?
View 8 Replies
View Related
Jan 2, 2012
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
View 9 Replies
View Related
Feb 12, 2013
I have two update queries in the same Proc. One Seems to run just fine, the other I am getting this error:
ORA-01427: single-row subquery returns more than one row
The working Updates' structure is the same as the erroneous one. This works:
UPDATE P0525_STOREROOM_HOLDER H
SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'NO REQUIREMENT' );
This one gives me a single-row error:
UPDATE P0525_STOREROOM_HOLDER H
SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'ABOVE ALLOWANCE'
AND H.NSN_QUANTITY > 0);
I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches...
View 4 Replies
View Related
Apr 5, 2011
I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.
I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?
My Package is the next one:
create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;
create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......
View 4 Replies
View Related
Sep 9, 2009
I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:
update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)
It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.
View 2 Replies
View Related
Sep 27, 2013
The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;
-- Incorrect indicative example 2.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;
View 4 Replies
View Related
Nov 20, 2009
want to update 230 records in ins_spr table but its returning error..
Sql statement in blue color returning 230 records.
------------------------------------------------
update ins_spr set
SPR_EXCC = 'NORSk'
where spr_code = (select distinct spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS') and spr_stuc in (select distinct a.sqe_stuc from srs_sqe a where a.SQE_EQEC = 'NP3M' and a.SQE_SQSC = 'BE' and a.sqe_stuc in ( select distinct b.sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
OR
update ins_spr set
SPR_EXCC = 'NORSK'
where spr_code = (select spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS')
and exists (select sqe_stuc from srs_sqe a where sqe_stuc = substr(spr_code,1,8) and SQE_EQEC = 'NP3M' and SQE_SQSC = 'BE'
and exists ( select sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
View 1 Replies
View Related
Oct 14, 2010
I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.
Here's a sample of the inner query results:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4
[code]....
The max(sessions) is 13, so what I want the final output to be is:
"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
Here is the create sql for the test data:
CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);
[code]....
Here is my query that works:
SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT
[code]....
View 2 Replies
View Related
Mar 2, 2011
The query below returns 101 rows. If I replace the column list with an asterik the query returns 892 rows. I do not understand why.
--select *
select Ref_Consultant_CD
,Resident_CD
,ID
,Ref_Facility_CD
[code]......
View 6 Replies
View Related
Oct 20, 2012
These are the tables I'm working with:
SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...
I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.
For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.
View 4 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
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
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
Nov 26, 2010
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
View 6 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
May 6, 2013
I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
Desired Output:
======
First Second Third
11 21 31
I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31
Is there any way to get the output in single line.
View 3 Replies
View Related
Apr 10, 2011
tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),
[Code]...
Data Returned from Select is:
1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown
Ideally what is required back is:
1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown
View 7 Replies
View Related
Aug 28, 2011
I want to update salary column of emp table in a way that every value of salary column be increased by 1000, is this possible I can do this one statement only??
(Just FYI-
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
)
[Code].....
View 2 Replies
View Related
May 6, 2010
I have a table formatted like this
ID|First Name|Second Name
1|X|X
2|X|X
3|X|X
4|X|X
and I want to insert First Name and Second Name records using the ID as the reference. The new data is currently in a .csv file, but I could put them in another table if that's easier.
ID|First Name|Second Name
1|Adam|Adamson
2|Ben|Benson
3|Chris|Christophers
4|Dave|Davidson
I understand how to do individual updates, I want to know how it is possible for me to do this as one query, as I have several thousand records to update.
View 14 Replies
View Related
Dec 12, 2011
how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this
update country1 set cname='japan','usa'
where cid=100,101
ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION
View 6 Replies
View Related
Jun 11, 2011
I have a requirement to load data into database column based on the value coming from input field.
E.g. :
Input:
EmpNo, DeptNo, Sal
1234, 10,1000
1234,20,2000
1234,30, 3000
1234,40,4000
2345,10,100
2345,20,200
2345,30,300
Output:
Emp No, Dept_10_Sal,Dept_20_Sal,Dept_30_Sal,Dept_40_Sal
1234, 1000, 2000,3000,4000
2345,100, 200, 300,0
Here we have more than 500 columns like this Dept_10_Sal, Dept_20_Sal........Dept_500_Sal.
View 32 Replies
View Related