1

there are two tables jobs and employees. i want to displace emloyees.first_name with jobs.job_title. wnen i do like this:

update employees set first_name= (select distinct job_title from jobs,employees where employees.job_id=jobs.job_id) QL Error: ORA-01427: single-row subquery returns more than one row occure.

tryed to do it with cursor, but there was the same error. Could you please tell me how to update muliple rows on the basis of another table?

MT0
  • 143,790
  • 11
  • 59
  • 117
Tom Cheng
  • 19
  • 6
  • 1
    It looks strange to set the employee's first name to a job title. Is that really what you want? – trincot Jun 25 '16 at 16:21

1 Answers1

2

When you use a correlated subquery, the outer table almost never goes in the correlated subquery. I imagine you really want:

update employees
    set first_name = (select job_title
                      from jobs
                      where employees.job_id = jobs.job_id
                     );

Setting a first name to a job title seems weird, but that seems to be the intention of your query.

I am guessing that job_id is unique for the table jobs, so this should fix your problem. If not, adding a and rownum = 1 would ensure that at most one row is returned.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • And generally you'd want to have a `where exists` so that you only update a row in `employees` if there is a corresponding row in `jobs`. – Justin Cave Jun 25 '16 at 17:11