Results 1 to 5 of 5

Thread: Update Query

  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Update Query

    Hi

    I am trying to do an update query in MS Access using SQL

    I have two tables Employees and Salaries.

    Employees
    Emp ID PK
    Dept
    Postion
    SALARY

    Salaries
    Emp Id PK
    HRLY rate
    SALARY

    I am trying to update the salary figure from the Salaries table into the employee table using sql. How would I do this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Update Query

    On Oracle I would do:

    update employees e
    set salary = (select salary from salaries s where s.emp_id = e.emp_id)
    where emp_id in (select emp_id from salaries);

    The final WHERE clause is to prevent overriding existing salary values with NULL where no matching salaries row exists. Omit the WHERE clause if that is what you would want to do.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    No Joy it told me to use an updatable query (which I had done!)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    joined update syntax varies from database to database

    in sql server, it's
    PHP Code:
    update table1
       set fldx 
    t2.fldy
      from table1 t1
    inner
      join table2 t2
        on t1
    .keyfld t2.keyfld
     where t2
    .fldz 'foo' 
    whereas in access it's
    PHP Code:
    update table1
    inner 
      join table2 
        on table1
    .keyfld table2.keyfld
       set table1
    .fldx table2.fldy
     where table2
    .fldz 'foo' 
    so for your example you would write
    PHP Code:
    update Employees
    inner 
      join Salaries 
        on Employees
    .EmpID Salaries.EmpID
       set Employees
    .SALARY Salaries.SALARY 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    3

    Unhappy

    Still won't work!

    I am being asked to supply a parameter value for employees.salary and then it tells me the table field is not updateable

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •