SQL & PL/SQL :: Update Table Field From Another?
Mar 28, 2011
I'm trying to update a field from another field in the same table.
I got a Table with 3 fields.
CREATE TABLE CONTENT
( LID_id NUMBER(6,0) NOT NULL ENABLE,
LPRECIS VARCHAR2(4000 CHAR),
LCOMMENT VARCHAR2(4000 CHAR)
)
How to update "LPRECIS" from "lCOMMENT" only where "LPRECIS" is NULL ?I used this statement but it's wrong.
UPDATE CONTENT
SET LPRECIS=(SELECT LCOMMENT
FROM CONTENT
WHERE LPRECIS IS NULL)
View 2 Replies
ADVERTISEMENT
May 3, 2013
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;
--code written on button to update those values which are checked.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......
View 5 Replies
View Related
Apr 23, 2009
I need to update a field with the 1st 9 characters of another field in the same record.
View 1 Replies
View Related
Jun 16, 2011
Trying my best to follow the posting guidelines.
There are two tables.
orderheader and orderdetails. orderheader is a master table and orderdetails is a detail table. So for every one record in orderheader table, there are many records in orderdetails table. I want to update a field in orderdetails table from a orderheader table using inner join.
update orderdetails
set orderdetails.conversion_dt =
(select orderheader.conversion_dt
from orderheader, orderdetails
where orderheader.number= orderdetails.number )
I get the following error:
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:
What mistake am I doing here?
View 16 Replies
View Related
Jun 29, 2010
I am trying to execute an UPDATE from a field that may have more than row. I get ORA-01427: single-row subquery returns more than one row.
I need to set the careof column in owner table to hold the name (NAM) from the alt table if the alttype from alt is ATT. We could have more than one id and the careof names can be different.
This is my query:
UPDATE owner s
SET s.careof = (SELECT a.NAM
FROM alt a
WHERE a.taxyr = '2011'
AND a.alttype = 'ATT'
AND a.card in ('0')
[code]....
View 1 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
Oct 30, 2011
i need to update field AA with zeros before the number (max 4 digits) for example: 1 = 0001 , 13 = 0013 , 1234 = 1234 .
View 1 Replies
View Related
Nov 14, 2011
I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
View 5 Replies
View Related
Jun 23, 2012
I have a table in which three field.
1)empid (Save employee code)
2)attendace_time (Save Date and inout time)
3)status. (Save in out Status).
I generate new form. which have four field.
1)empid
2)attendance_date base on attendance_time 3
3)Attendace_time base on attendance_time
4)Status .
Problem is that when i update the Attendace_time . date save first date of the month. when i update the attendance_date time save 12:00
View 1 Replies
View Related
Jul 9, 2012
I'm trying to restrict update in a specific field in my form . I have set its property(update allowed) to no and still the field is update able. I am also having on-commit trigger in my form level that well display a message and followed by the command commit. is it possible the trigger is overriding the property(update restriction).
View 9 Replies
View Related
Aug 6, 2013
I have seen in probably VB or Dot net software being used at some premises that there "formula computation is live and interactive".
What i means that suppose a cashier has made invoice having total of 83$. Mow in the text box for entering amount received, the cashier is about to enter 100 but surprisingly the returned value/remaining value (total invoice - amount received) is being computer LIVE.
This is what happens.
1) The cashier entered 1 and the remaining value instantly showed 82 (83-1)
2) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 73 (83-10)
3) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 17 (83-100)
Hence a continuous updation/changing of the remaining value on every key stroke is observed.
View 3 Replies
View Related
Apr 29, 2011
What i am required to do is to protect a field 'COUR_CODE' from update when the SHIP_METHOD='R'. c
if :system.record_status = 'INSERT' then
declare
vcour_code ACCOUNTS.cour_code%type := NULL;
vcourier_account_no ACCOUNTS.courier_account_no%type := NULL;
canvas_name varchar2(30);
begin
[code]....
View 11 Replies
View Related
Oct 4, 2011
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
View 7 Replies
View Related
Nov 26, 2012
We are using APEX 4.2, 10GR2, RedHat5.
I have a report that comes from SQL Query (updateable report). I'm using the apex_item.text and apex_item.hidden on fields. I'm using a button to submit and after submit process to add some logic that I need.
There could be 1 - 10 records in the report. There is only 1 field that is needed to enter a value, but the value of this field determines the value of another field. I think that I can do this with a submit button and an after submit process where I loop through all the records. I think I have this handled.
This is the question
When the value of that field is changed then the value of another field in the same row changes immediately. All the examples I've seen so far are for a single record and that doesn't work for us.
I guess this is a MRU process but I haven't seen an example where a dynamic action is possible on a Multi Row Update.
View 4 Replies
View Related
Oct 18, 2012
Let's say that we have an order entry app and the price and quantity items are already populated. We now want to show the user the extended price (the product of the first two items). the extended price would automatically display after both the quantity and price items are entered. I'm assuming that would require some kind of client-side processing. I imagine server-side processing is simpler, so I'll lean in that direction. I'm sure we've all seen web sites that require you to click a button for the order totals to be updated. I'm okay with that approach, but I haven't quite put all of the pieces together. Here's what I came up with.
A region button was added to trigger the updating of the extended price. The button's "Action When Button Clicked" is to submit the page. Under "Page Processing", a process was created to do the calculation with the recipient of the product being a page item. From debug code in the procedure, I can see that the item containing the extended price was, indeed, modified. But the new value doesn't display in the field and the Session window shows that the value of the item didn't change.
I must be missing something. The debug code says the item value was updated. The Session window says it wasn't updated. What gives? What did I do wrong and what needs to be done to correct this?
View 3 Replies
View Related
Mar 6, 2013
Environment:
Apex 4.2
DB 11gR2
I've started using Apex 4.2(new to Apex). I have built a form based on a table with a date column on it. The form allows update on all fields/columns of the base table. For the date field/column, the goal is to present the user with sysdate on load and update the date with sysdate if the record is changed any of the fields. How can I accomplish this?
View 7 Replies
View Related
May 14, 2010
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?
View 4 Replies
View Related
Jun 28, 2012
I'm trying to update a number field on an apex page by summing up the values of multiple page items. I've tried to follow this tutorial
[URL]........
especially the "Create a Set Value Dynamic Action Using PL/SQL" part.
In the tutorial example they return an page item value P3_SAL multiplied by a multiplier they've determined by another page item's case. What I want to do is much simpler in that I just want to add up multiple page item values and then display them in a number field at the bottom of the page.
so the code I tried to use based on the tutorial is in the tab: Home >Application Builder>Application 103>Page 3>Edit Dynamic Action>Create / Edit Action
Set Type: PL/SQL Function Body
PL/SQL Function Body:
BEGIN
return :P3_ITEM1+P3_ITEM2+P3_ITEM3;
END;
Page Items to Submit: P3_ITEM1,P3_ITEM2,P3_ITEM3
But it gives me an error that I need to declare the identifiers, yet it works if I only use one item and I can perform any arithmetic.
i.e.
BEGIN
return :P3_ITEM1+1000;
END;
View 2 Replies
View Related
Jul 21, 2011
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
/
View 9 Replies
View Related
May 29, 2012
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;
/
View 3 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 16, 2013
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies
View Related
Aug 24, 2013
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
View 2 Replies
View Related
Jul 5, 2012
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?
PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed
View 3 Replies
View Related
Feb 6, 2011
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
View 4 Replies
View Related
Dec 28, 2010
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
create materialized view log on my_view;
and then schedule in a dbms_jobs?
View 1 Replies
View Related
Dec 27, 2006
We have a table with several columns (id, title, description, area).The data in the table looks like this
1 sometitle1 description1 USA
2 sometitle2 description2 Germany
3 sometitle3 description3 Japan
4 sometitle4 description4 Honduras
We have Oracle users with their usernames and password. We would like to lock every single user to a certain area.Example:
user 1 may see only records, where AREA=USA
user 2 may see only records, where AREA=Honduras
How can I do this in Oracle. I am using Oracle Enterprise 9.2
View 1 Replies
View Related
Nov 27, 2012
I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.
CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801;
SELECT * FROM WC_BOOKING_SALESREP_F PARTITION(SALESREP_LESS1_99991231);
Here is the Explain Plan for the same.
CODESELECT STATEMENT ALL_ROWSCost: 1,501 Bytes: 293,923,641 Cardinality: 809,707
4 PX COORDINATOR
[code]....
How do I know if the sql is doing partition prune.
View 1 Replies
View Related
Aug 20, 2012
select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies
View Related
May 31, 2012
What I need to do is redirect an insert to another table when a field has some value. This is what I have:
CREATE TABLE TEST.TEAMS
(
NAME VARCHAR2(255 CHAR),
[Code]....
This is the result when I try to insert a record
INSERT INTO TEST.TEAMS ( NAME, LOCATION, PLACE ) VALUES (
'Belgrano', 'Cordoba', 1);
ORA-04091: table TEST.TEAMS is mutating, trigger/function may not see it
View 5 Replies
View Related
