Updating values in MySQL table

Question

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.

Dresden · Accepted Answer · 2016-05-24 00:08:16Z

5

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;

edited May 24, 2016 at 0:08

answered May 23, 2016 at 23:38

Dresden

5702 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yeezus Over a year ago

I get an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE emp SET emp.deptno = (SELECT dept.depno FROM dept WHERE dname = "Sales") ' at line 4

Dresden Over a year ago

So your column name in one of your tables is "depno" instead of "deptno"? So it's working after the small change?

Yeezus Over a year ago

No just altered the table and I'm still getting the error,, even recreated the database.

Dresden Over a year ago

Here's a SQL Fiddle with my query and a really hastily created schema and data insert sqlfiddle.com/#!9/b5e8c/1. It should work unless your empno is potentially a VARCHAR or something that requires quotations around it. Could you provide the error you're getting?

Yeezus Over a year ago

I'm using mysql on mac book pro (mid 2010) , os x el capitan 10.11.5 and I managed to get it to work by just using the second part of the query; starting from UPDATE so al is well. Thank You Dresden!!

Collectives™ on Stack Overflow

Updating values in MySQL table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related