Yeezus Yeezus - 6 months ago 8
MySQL Question

Updating values in MySQL table

Given These two tables:

emp dept

------|--------|--------| |--------|------------|----------|
empno | name | deptno | | deptno | dname | loc |
------|--------|--------| ----------------------|----------|
7566 | Jones | 20 | | 10 | Accounting | New York |
7654 | Martin | 30 | | 20 | Research | Dallas |
7689 | Blake | 30 | | 30 | Sales | Chicago |
7782 | Clark | 10 | ----------------------------------
7839 | King | 10 |
-------------------------


One of the questions is :

Employee 'Clark' has been transferred to the Sales department. Use
the appropriate query to reflect this change in the amp table. Make
use of a subquery to determine the department number of the Sales
department.


(just discovered the data used in these tables is from sql cookbook)

Anyways I'm new to SQL , and I'm using MySQL to complete this question. I Thought about using an UPDATE query but I can't figure out how to incorporate the query for determining the department number.

Answer

First you would select the deptno for a sales position (you'd get 30 for Sales):

SELECT deptno
FROM dept
WHERE dname = 'Sales'

Then you would take this deptno and use it as the value you're going to set deptno to in emp for the person named Clark with empno = 7782:

UPDATE emp SET emp.deptno = 
    (SELECT dept.deptno
     FROM dept
     WHERE dname = 'Sales')
WHERE empno = 7782;