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
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 RelatedActully 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
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.
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"?
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.
i 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.
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 ;
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 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?
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;
/
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?
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
/
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]....
In 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
How to update two tables in single set or single query ?
View 8 Replies View RelatedCan 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;
I 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.
We 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 RelatedI 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...
I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
View 32 Replies View RelatedI 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.
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
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.
Suppose I have a table.
SQL> select * from emp;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
1 RN 10 10
3 C 20 40
4 A 10 20
5 B 20 100
6 D 10 11
7 S 30 300
6 rows selected
Now I need to update the table as for all employees will get sal 100 for depetno=10 and employees of deptno=20 will get sal=200. I need to update this with a single query.
It started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:
update contact
set name = 'Name1'
where row_id = 'row_id1'
update contact
set name = 'Name2'
where row_id = 'row_id2'
update contact
set name = 'Name3'
where row_id = 'row_id3'
My DB is oracle 10, and TOAD is version 9.
I'm updating a big table with an other one (see topic
[URL]........
I was wondering if I could do it without using the WHERE EXISTS condition. Let me explain better:
create table TEST
(
ENT_SIG VARCHAR2(10),
EMP_ID VARCHAR2(10),
DATE_START DATE,
DATE_END DATE
);
create table TEST2
(
ENT_SIG VARCHAR2(10),
EMP_ID VARCHAR2(10),
DATE_START DATE,
DATE_END DATE
);
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END)
values ('014', '120', TO_DATE('20080101','YYYYMMDD'), TO_DATE('20080131','YYYYMMDD'));
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END)
values ('014', '121', TO_DATE('20080201','YYYYMMDD'), TO_DATE('20080228','YYYYMMDD'));
[code].....
I'm asking if there's a way to do this:
update test a
set (date_Start, date_end) = (select date_start, date_end
from test2 b
where b.emp_id = a.emp_id
[code].......
Without using the WHERE EXISTS. I don't want to make two accesses to table2, I would like instead to do something like "If subquery returns a row, use it, else keep what you have in destination table".
Is there a way to do it without entering test2 twice?
I need to write a script which copies 4 col data from one table to another table. there are three tables
cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.
cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.
So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.
I 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
I 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]....
