update [message #602565] |
Thu, 05 December 2013 13:39 |
|
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 |
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 |
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 #602633 is a reply to message #602575] |
Fri, 06 December 2013 02:32 |
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.
|
|
|