SQL code for identifying salary for a status

selecting

Before updateing a table, you've probably learnt that you'll need to first simulate your desired resultset, which can be universally done with a select (or do the update directly in a transaction that you rollback, but this won't be universal).

From a join

As you have tagged your question "join", I suppose this is something you want to try.

You'll naturally select from DEPT join EMP on DEPT.dept_no = EMP.dept_no, but if you select * from it, you'll end up with your two columns C and N from DEPT, which is probably not what you want: you desire 1 column out of those 2, because in the end you want to update 1 column.

The SQL keyword that returns a column from a "switch" over successive conditions is case.

As you'll want the output column MeritedSalary to be either DEPT.C or DEPT.N, depending on the value of EMP.Task_status, you'll write this case as:

case
    when EMP.Task_status = 'C' then DEPT.C
    when EMP.Task_status = 'N' then DEPT.N
end

All in all, if you run:

select
  EMP.*,
  case
    when EMP.Task_status = 'C' then DEPT.C
    when EMP.Task_status = 'N' then DEPT.N
  end as MeritedSalary
from EMP
join DEPT on DEPT.dept_no = EMP.dept_no;

You'll get:

From a correlated subquery

join is particularly useful when you need multiple columns of both tables, but when you want only one column of one of the tables, you can dirtily rely on correlated subqueries: the table you need only one column from, can be a sub-select (select EMP.*, (select …) as MeritedSalary from EMP), with only two conditions: that the subselect returns exactly 1 column and at most 1 row.

Which is the case here (we only want 1 column of DEPT to appear as the MeritedSalary).

And we can take advantage of the sub-select knowing the tables of the super-select (but not the other way round!) so we can make it a correlated subquery, that is, a query that will return something depending on the row of EMP it is currently reading.

Thus you could transform our query into:

select
  EMP.*,
  (
    select
      case
        when EMP.Task_status = 'C' then DEPT.C
        when EMP.Task_status = 'N' then DEPT.N
      end as MeritedSalary
    from DEPT
    where DEPT.dept_no = EMP.dept_no
  )
from EMP;

and get the same results as above.

updateing

Now updating from another table is something not so standard in SQL.

… However you can update from a correlated subquery, and subtly transforming the above query into an update (where the results of the subquery, that was displayed as MeritedSalary, now becomes the value to set Salary to):

update EMP
set Salary =
(
  select
    case
      when EMP.Task_status = 'C' then DEPT.C
      when EMP.Task_status = 'N' then DEPT.N
    end
  from DEPT
  where DEPT.dept_no = EMP.dept_no
);

Resulting in:

And this universal although not optimized query works on PostgreSQL, SQLite, Oracle, MariaDB, SQL Server…

It's difficult to tell exactly what you want, but I think you are saying that you want to update the salary column in the employee column based on a value in the department table which further depends on whether or not the job is complete or not.

1. First you need to join the tables based on dept_no which is common to both tables

2. Next you need to identify which column you want to reference using a case statement.

3. Finally, you need to UPDATE the table with the new data.

Putting it together, you something like the following (which would work on MySQL 5.7)

UPDATE EMP join DEPT on EMP.dept_no=DEPT.dept_no 
SET EMP.salary = CASE WHEN EMP.Task_status='c' THEN DEPT.c ELSE DEPT.n END;

see https://www.db-fiddle.com/f/s9SMfiRu6Tz32ESJh4QmSe/0 for data