Home » SQL & PL/SQL » SQL & PL/SQL » update (oracle 10g)
update [message #602565] Thu, 05 December 2013 13:39 Go to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Hi,
I am trying to update emp2 table using rowtype.

In emp2 table I inserted only empno after that am trying to update remaining columns based on empno.

It is executing successfully but data is not updating the database.

Help me.

declare
v_emp emp2%ROWTYPE;
begin
v_emp.ENAME :='RAJESH';
v_emp.JOB :='SALESMAN';
v_emp.HIREDATE :='12-JAN-2013';
v_emp.MGR :=7839;
v_emp.SAL :=2000;
v_emp.DEPTNO :=20;
v_emp.COMM :=0;
  update emp2 set
    ename = v_emp.ENAME,
    job = v_emp.JOB,
    hiredate = v_emp.HIREDATE,
    mgr = v_emp.MGR,
    sal = v_emp.SAL,
    deptno = v_emp.DEPTNO,
    comm = v_emp.COMM
  where empno = v_emp.empno 
  and v_emp.empno = 1345;
  commit;
END;



Thanks in Advance.
The Learner.
Re: update [message #602568 is a reply to message #602565] Thu, 05 December 2013 13:57 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Your procedure doesn't assign a value to v_emp.empno, therefore at the point of update, it is null, therefor your where clause excludes all rows from the update.
Re: update [message #602569 is a reply to message #602565] Thu, 05 December 2013 13:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
It should be done using plain SQL. The entire anonymous block is nothing but a simple update statment:

UPDATE emp2 
SET    ename = 'RAJESH', 
       job = 'SALESMAN', 
       hiredate = to_date('12-JAN-2013','DD-MON-YYYY'), 
       mgr = 7839, 
       sal = 2000, 
       deptno = 20, 
       comm = 0 
WHERE  empno = 1345;


Another thing, assigning HIREDATE with value as '12-JAN-2013' is actually not assigning a date, inside those single quotes, it is a string and not a date.


[EDITED by LF: removed quote of the whole original message]

[Updated on: Sat, 08 March 2014 01:45] by Moderator

Report message to a moderator

Re: update [message #602575 is a reply to message #602569] Thu, 05 December 2013 14:32 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
declare
v_emp emp2%ROWTYPE;
begin
v_emp.ENAME :='SHANKAR';
v_emp.JOB :='SALESMAN';
v_emp.HIREDATE :='12-JAN-2013';
v_emp.MGR :=7839;
v_emp.SAL :=2500;
v_emp.DEPTNO :=20;
v_emp.COMM :=10;
v_emp.empno:=1345;
  update emp2 set
    ename = v_emp.ENAME,
    job = v_emp.JOB,
    hiredate = v_emp.HIREDATE,
    mgr = v_emp.MGR,
    sal = v_emp.SAL,
    deptno = v_emp.DEPTNO,
    comm = v_emp.COMM
  where empno = v_emp.empno; 
--  and v_emp.empno = 1345;
  commit;
END;


Thank you pablolee.
Re: update [message #602633 is a reply to message #602575] Fri, 06 December 2013 02:32 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Now you should address the issues that Lalit pointed out. Presumably you are just using this as practice, nothing wrong with that, but the point about the date field you REALLY should pay attention to that.
Previous Topic: gettin ip addres and user name
Next Topic: Difference in the execution plans
Goto Forum:
  


Current Time: Thu Mar 28 21:13:13 CDT 2024