SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query
Nov 22, 2011How to update two tables in single set or single query ?
View 8 Replies
ADVERTISEMENT
PL/SQL :: Update A Single Column Data In 10 Tables?
Oct 2, 2012We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies View RelatedSQL & PL/SQL :: Update Two Table Column In Single Query
May 25, 2012How 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 ;
SQL & PL/SQL :: Update Multiple Table In Single Query
Jan 27, 2012Actully 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
SQL & PL/SQL :: How To Update Master And Detail Table In Single Query
Jan 18, 2012I 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
SQL & PL/SQL :: Insert Multiple Records In Different Tables At A Time (Using Single Query)
Jun 8, 2010 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?
PL/SQL :: Update SAME Column Name In Two Tables From ONE Query
Oct 15, 2012is it possible to update a same column name in two tables.
I have two tables in same schema
(1)table name
pem.igp_parent
column name
igp_no.
igp_type
(2)table name
pem.igp_child
column name
igp_no.
igp_type
i want to update igp_no column in one query, how it would be possible.
PL/SQL :: Update Multiple Columns With Single Update Statement
May 30, 2013i 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 RelatedSQL & PL/SQL :: How To Update Multiple Table With Single UPDATE
Jul 19, 2011I 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 RelatedSingle Row Subquery Errors During Update?
May 22, 2012I'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]....
SQL & PL/SQL :: Update Name With Single Space Between Words
Mar 26, 2010In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
I am giving the data as follows.
CREATE TABLE student(sname VARCHAR2(30));
INSERT INTO student VALUES('PRAKASH BABU');
INSERT INTO student VALUES('RAJESH KUMAR');
INSERT INTO student VALUES('POORNA CHANDAR');
INSERT INTO student VALUES('ANIL RAJ');
INSERT INTO student VALUES('ABHI RAM KONA');
INSERT INTO student VALUES('SRI TEJA MEDI');
Like this the table contains millions of rows.
I want to update the names like this.
PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI
SQL & PL/SQL :: Update Single Column In X Table
Sep 2, 2011i need a query for update..the logic is
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.
SQL & PL/SQL :: How To Write Single WITH For A Update And Delete
Mar 13, 2012Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4
using
(select table_3.col1 col1,table_3.col2 col2,
from table_1, table_2, table_3
where
[code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3
where (table_3.col1,table_3.col2)
in ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 )
update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2
delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
Forms :: How To Insert And Update With Single Statement
Mar 5, 2012I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
SQL & PL/SQL :: Update Queries / Single Row Subquery Returns More Than One Row
Feb 12, 2013I 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...
Update Multiple Rows With Different Values In A Single Statement
Jul 24, 2009Updating 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.
SQL & PL/SQL :: Updating Multiple Rows With Single Update Statement?
Aug 20, 2013create 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"?
Forms :: Insert / Update And Delete Single Record At A Time?
Aug 10, 2005I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
View 32 Replies View RelatedCreating And Dropping Global Temp Tables Inside A Single Procedure
Aug 20, 2010I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
SQL & PL/SQL :: Update With Join Over Two Tables
Apr 28, 2010I am trying to write an Update that really frustrates me because it won't work for one reason or another.The situation is that I have two tables for customer information, t1 with the names of the customer and t2 with the address.These two can be joined via a client_id.
Now I have a third table t3 with the name and address of potential customers. I want to find out if some of them are already known to me so that I can update the client_id from table t1 or t2 into t3.
I have to join firstname, lastname from t3 to firstname, lastname from t1 and street, zip, city from t3 to street, zip, city from t2 and client_id from t1 to t2. Additional there is the problem that there can be more than one result so I have to update one of the found client_ids per name/address into t3.I am no expert to PL/SQL, I just know what SQL works in Access and that is:
UPDATE (t3 INNER JOIN t1
ON (t3.firstname= t1.firstname) AND (t3.lastname = t1.lastname)) INNER JOIN t2
ON (t3.city = t2.city) AND (t3.zip = t2.zip) AND (t3.street = t2.street) AND (t1.client_id = t2.client_id)
SET t3.client_id = t1.client_id;
SQL & PL/SQL :: Bulk Update - Two Tables
Dec 5, 2011I am trying to write an update using two tables, one table contains the list of updates to be applied, and the second contains the old data. I have a working solution but I was wondering if there might be some other better way to accomplish this task.
CREATE TABLE foo
(
foo_id NUMBER,
foo_val NUMBER
);
CREATE TABLE foo_change
(
foo_id NUMBER,
foo_val NUMBER
[code]...
The update works, but I thought there may be a better way I just don't know about.
SQL & PL/SQL :: Update Statement By Joining 2 Tables
Mar 24, 2012I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values
NEW_NAME OLD_NAME
DRUG10 DRUG1
I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:
DRUG1,DRUG2,DRUG3,DRUG4,DRUG5
Each DRUG has multiple rows associated with it.
I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.
However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.
DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows
(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.
UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.
SQL & PL/SQL :: Update Statement Using Multiple Tables
Aug 26, 2010I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword
SQL & PL/SQL :: After Update Trigger (with Multiple Tables)
Nov 21, 2012I have to write a "after update trigger". Here, i have to update the stock table by other inventory tables (by complex query).
I have written trigger below. how to make it correct?
create or replace trigger trg_stk_upd_pur
after update on O_STOCK_EFFECTS
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
[Code]....
PL/SQL :: Lock Row In 1 Table While Update Other Tables
Oct 15, 2012I want to read 1 table. If the date is less than today, I want to update 4 other tables. I only want to do this update once a day.
While the 4 other tables are being updated, I want the other web users to pause for the update while this procedure runs.
Is there a better way to do this?
Here is what I have:
CREATE OR REPLACE PROCEDURE TEST_TODAY2 AS
-- to create the table
-- create table test_today(updated_date date);
-- insert into test_today(updated_date) values (sysdate-1);
-- select * from test_today;
cursor daily_update_cur is
select updated_date from test_today
for update of updated_date;
[Code]...
Please use {noformat}{noformat} tags before and after your code as described in the FAQ: {message:id=9360002}.
I've corrected it this time for you.
SQL & PL/SQL :: Loop Over Recordsets In Update Tables
Jan 21, 2011I can't seem to get away from writing php scripts to handle the update. I want to learn to use procedures more. What I have is a table like this:
courses(
id number(16) pk,
Division_title varchar2(100),
department_title varchar2(100)
I also have a temp table where I upload updated data into from time to time it looks like this
load_updates_courses(
id number(16) pk,
div_desc varchar2(100),
dep_desc varchar2(100)
)
Currently when I need to update the courses table, I write a php script to do the update. But what I really would like to do is write a procedure I could call to handle this.
I figured I need to loop over the recordsets in the update tables then do a update but I can't figure out how to get started with the plsql.
SQL & PL/SQL :: Single Row Query Returns More Than One Row?
Oct 3, 2010I am getting an error "Single row query returns more than one row" in an Exception block.
But in subqueries I am using IN operator not using =.
And I don't have duplicates rows in tables.
SQL & PL/SQL :: Getting Total And Single Name In One Query?
Oct 29, 2013I want to get any employee name of deptno 10 but total count of number of employees under dept 10.
DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;
PL/SQL :: Single Row Query Returns More Than One Row?
Mar 12, 2013this is my sql=
" SELECT
hpn.id AS id,
hpn.psn_id,
hpn.last_name,
[Code].....
"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,
but it returns error single row query returns more than one row, because one people can have more that one card_number.
PL/SQL :: Query To Find Single Row?
Oct 5, 2012i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.
ID Course
103812 CFH
102968 REP
103812 DFH
102968 DFH
103071 DFH
102968 CFH