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

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)

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.