-
problem with update
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
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';
-
Can't actually follow the logic of your code but I assume you are looking for something like:
HTML Code:
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>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Some snafu's:
Actuallly you need different variable names for the old and new project id's:
Code:
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';
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|