Skip to content
Advertisement

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

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.

And this is the error I’m getting.

#1242 – Subquery returns more than 1 row

I would really appreciate some help.

Advertisement

Answer

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):

So try this:

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

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement