problem with update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: problem with update

  1. #1
    Join Date
    Nov 2008
    Posts
    1

    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';

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Talking 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
  •  



Click Here to Expand Forum to Full Width