How to insert values into a column in table B based on values in table A

Question

I'm fairly new to SQL and only know basic commands right now. I'm trying to get a value for a column 'position' in table 'role' based on the value of column 'salary' in table 'payroll'.

Table 'role' and 'payroll' contain three common columns 'employee_ID', 'first_name' and 'last_name' with common values. I want to use another column 'salary' in table 'payroll' to determine the output for column 'position' in table 'role'.

For example, if the 'salary' column in table 'payroll' has a value less than 2000, I want the 'position' column in table 'role' to display 'janitor'.

This is what I'm using.

update role 
set position = 'janitor' 
where (select salary from payroll) > 2000

And this is the error I'm getting.

#1242 - Subquery returns more than 1 row

I would really appreciate some help.

Could you please clarify how tables role and payroll are related to each other? — Sergey, Jan 04 '21 at 19:02
You need to define the `JOIN` criteria which tells the database engine how to associate each `position` row with each `payroll` row. Even if you have an FK relationship defined, SQL does not use those to infer a _natural join_ - you always need to be explicit. — Dai, Jan 04 '21 at 19:07
Table 'role' and 'payroll' contain three common columns 'employee_ID', 'first_name' and 'last_name' with common values. I want to use another column 'salary' in table 'payroll' to determine the output for column 'position' in table 'role' — SQLrookie, Jan 04 '21 at 19:13
Not sure, if this syntax is supported by MySQL UPDATE R SET R.POSITION='JANITOR' FROM ROLE AS R JOIN PAYROLL AS P ON R.EMPLOYEE_ID=P.EMPLOYEE_ID AND R.FIRST_NAME=P.FIRST_NAME AND R.LAST_NAME=P.LAST_NAME WHERE P.SALARY>2000 — Sergey, Jan 04 '21 at 19:17
I tried it but got an error. #1064 - 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 'FROM ROLE AS R JOIN PAYROLL AS P ON R.EMPLOYEE_ID=P.EMPLOYEE_ID AND R.FIRST_NAME' at line 1 — SQLrookie, Jan 04 '21 at 19:22

Dai · Accepted Answer · 2021-01-04T19:37:32.070

MySQL doesn't use a FROM keyword when doing an UPDATE from a query.

The MySQL syntax is this (parts in square-brackets are optional, parts in angle-brackets are placeholders):

UPDATE <table_references>
SET <assignment_list>
[WHERE <where_condition>]

-- <table_references> is defined as:
tableName1 [ AS <alias1> ] [ <join_operator> tableName2 [ AS <alias2> ] ON <join_criteria> ] [...]

So try this:

UPDATE
    role AS r
    INNER JOIN payroll AS p ON p.employee_id = r.employee_id
SET
    position = 'janitor'
WHERE
    p.salary > 2000

Another approach is to use a temporary-table with a WHERE IN predicate:

CREATE TEMPORARY TABLE employeeIds ( employeeId int NOT NULL );
INSERT INTO employeeIds ( employeeId )
SELECT employee_id FROM payroll WHERE salary > 2000;

UPDATE
    role
SET
    position = 'janitor'
WHERE
    employee_id IN ( SELECT employee_id FROM employeeIds );

The temporary table employeeIds will be deleted when you disconnect from the database - I assume you're using MySQL Workbench or similar. I don't know what the connection lifetime semantics are when using a web-based tool like phpMyAdmin, however.

Thank you so much. That worked perfectly. I then used it to calculate the average salary of all positions titled x. select avg(salary) from payroll as p INNER JOIN role AS r ON r.employee_id = p.employee_id WHERE r.position = 'data_analyst' — SQLrookie, Jan 04 '21 at 19:49

How to insert values into a column in table B based on values in table A

1 Answers1