problem with update

[mosh]

Iam having some trouble with updating my table. When I open the file using start, it prompts like desired, and also indicates that the rows have been updated but nothing is updated. I hope anyone can help me..Here is the code

set verify off

accept employeeName prompt 'Enter Employee Name to Change Project:'
accept EMP_projectID prompt 'Enter Old Project ID for &employeeName:'
accept EMP_projectID prompt 'Enter New Project ID:'
accept empID prompt 'Enter the Employee ID:'


prompt
accept employeeName prompt 'Enter Employee Name to Change Project:'
accept EMP_projectID1 prompt 'Enter Old Project ID for &employeeName:'
accept EMP_projectID1 prompt 'Enter New Project ID:'

UPDATE  employee
set EMP_projectID=replace(EMP_projectID,'&EMP_projectID','&EMP_projectID')
where employeeName='&employeeName';

UPDATE  employee
set EMP_projectID=replace(EMP_projectID,'&EMP_projectID','&EMP_projectID')
where employeeName='&employeeName'
and employeeID='&empID';

[PAVB]

Can't actually follow the logic of your code but I assume you are looking for something like:

SQL> set verify off
SQL> 
SQL> create table test999(empId number, empProject number);

Table created.

SQL> insert into test999 values(1, null);

1 row created.

SQL> insert into test999 values(2, null);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test999;

     EMPID EMPPROJECT
---------- ----------
         1
         2

SQL> accept empId prompt 'Enter empId:'
Enter empId:1
SQL> accept empProject prompt 'Enter empProject:'
Enter empProject:88888
SQL> 
SQL> update  test999
  2  set     empProject='&empProject'
  3  where   empId='&empId';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test999;

     EMPID EMPPROJECT
---------- ----------
         1      88888
         2

SQL>

[LKBrwn_DBA]

Actuallly you need different variable names for the old and new project id's:

accept employeeName prompt 'Enter Employee Name to Change Project:'
accept EMP_projectID_old prompt 'Enter Old Project ID for &employeeName:'
accept EMP_projectID_new prompt 'Enter New Project ID:'
accept empID prompt 'Enter the Employee ID:'


UPDATE employee
   SET emp_projectid = '&&EMP_projectID_new'
 WHERE employeeid = '&&empID' 
   AND emp_projectid = '&&EMP_projectID_old';