Update command???

[data78]

2 tables: Employee1 (emp_ssn, empl_name, dept_no) and Employee2 (empl_ssn,dept_no)
The Employee1 table does not have data for dept_no. However Employee2 table does. How do I get dept_no data in Employee2 table over to Employee1 table? (you're updating multiple rows in Employee1 table.)

< ERROR: SQL command not properly ended, line 3>
1. update employee1
2. set employee1.dept_no=employee2.dept_no
3. from employee1, employee2
4. where employee1.ssn=employee2.ssn;

Thanks, I've been stuck on this for awhile.

 

[jimbopalmer]

update employee1 set employee1.dept_no=(
select employee2.dept_no
from employee2
where employee1.ssn=employee2.ssn); I tried to remain child-like, all I acheived was childish.

 

[data78]

I tried above, &quot; single-row subquery returns more than one row.&quot; I replaced the equal sign with &quot;in&quot; and got &quot;missing equal sign.&quot;

 

[Turkbear]

Hi, Apparently one employee ( identified by ssn )
can have more than one dept_no [so the query returns 1 record for each dept_no in employee2] ( or there are duplicate records in employee2)...Either case will be a problem - to use your query you would need to restrict the matching records to 1 and only 1 in some way.
You can use a MAX function ( although it will result in just the max dept_no being listed for the employee in employee1):

update employee1 set employee1.dept_no=( 
select MAX(employee2.dept_no)
from employee2
where employee1.ssn=employee2.ssn
group by employee2.ssn);

If employee2 has all the info you need, why not truncate employee1 and use :

insert into employee1 select * from employee2;

 

[data78]

I think the problem is that the sub query returns all (multiple)rows that fit the condition a.ssn=b.ssn. The equal signs expects a single row to be returned. I thought to solve this you would use &quot;IN&quot; instaed of =.