Results 1 to 3 of 3

Thread: Update Query?

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Unanswered: Update Query?

    Hi,

    I'm new to Oracle and SQL and am a bit stuck on a simple query.

    I'm trying to pay supervisors 50,000 per year but I'm running into problems:

    INSERT INTO employee
    VALUES(‘10000’,’00001’, NULL, ‘Bob’, ‘Jones’, ‘18’, ‘Croft Lane’, ‘Stonehaven’, ‘Scotland’, ‘DD46YH’, ’10-OCT-54’, ‘10000.00’, ‘01234543210’);

    COMMIT;

    INSERT INTO employee
    VALUES(‘10001’,’00001’, ‘10000’, ‘Susan’, ‘Smith’, ‘87’, ‘Long Path’, ‘Glasgow’, ‘Scotland’, ‘EH56JB’, ’29-JUN-72’, ‘42000.00’, ‘09876543211’);

    The third column of the second INSERT is the supervisorID so Bob supervises Susan.

    I have got this far with the query:


    UPDATE employee SET salary=50000
    WHERE supervisorID IS NOT NULL
    AND supervisorID =
    (SELECT empID FROM employee);

    but this just updates Susans wage, I can see how it does but I cannot find out how to make bob (the supervisor) get the wage he deserves.

    P.S Names etc. have been changed.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    If someone is a supervisor when his empID exists anywhere in the supervisorID column, the update statement would be:

    UPDATE employee SET salary = 50000
    WHERE empID in (SELECT supervisorID from employee)

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, you're assuming that we all know "employee" table description. But we don't. Third column is 'supervisorID', but the rest is up to us to guess. So I guess first column is 'empID'. Is it?

    Writing INSERTs is much more clear if you include column names, such as

    INSERT INTO empliyee
    (empID, some_column, supervisorID, emp_name, emp_surname, ...)
    VALUES
    (100, '001', null, 'Bob', 'Jones', ...);

    This will help not only other people, but you too when debugging the query. Do you really believe you'll remember all column names in INSERT statements next year? Some of them you might, and some you won't. Also, what if the table descrption changes? If you add another NOT NULL column? You'll be in trouble.

    Now, about your query: UPDATE you wrote is kind of lucky as it is a good candidate to end up with the TOO-MANY-ROWS error in line 'supervisorID = (SELECT empID ...)'. Better choice would be 'IN' instead of '='. Therefore, such a query might help you:

    UPDATE employee SET
    salary = 50000
    WHERE empID IN (SELECT supervisorID FROM employee);

    This will give 50000 to every employee whose ID is among supervisorID's in the table.

Posting Permissions

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