Sqlite: Update from multiple columns from another table with more than one conditin

Question

I have 2 tables, KPIs_AdminStaff and KPIs_AdminStaff_Scores. I want to update one column (Objective_Score) in the first one using data from multiple columns (OBJECTIVE_ID_1, OBJECTIVE_ID_2,....) in the second one . The conditions are: KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID and Admin.OBJECTIVE_ID = (2352, 2355, 2401,2402,2409) respectively.

When I apply the below query it only updates KPIs_AdminStaff_Scores.ID = 2409

This is the code I tried based on this solution: Update table values from another table with the same user name

UPDATE KPIs_AdminStaff 
SET OBJECTIVE_SCORE =
(
SELECT OBJECTIVE_ID_1
FROM KPIs_AdminStaff_Scores 
WHERE KPIs_AdminStaff.OBJECTIVE_ID = 2352
AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
,
OBJECTIVE_SCORE =
(
SELECT OBJECTIVE_ID_2
FROM KPIs_AdminStaff_Scores 
WHERE KPIs_AdminStaff.OBJECTIVE_ID = 2355
AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
,
OBJECTIVE_SCORE =
(
SELECT OBJECTIVE_ID_3
FROM KPIs_AdminStaff_Scores 
WHERE KPIs_AdminStaff.OBJECTIVE_ID = 2401
AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
,
OBJECTIVE_SCORE =
(
SELECT OBJECTIVE_ID_4
FROM KPIs_AdminStaff_Scores 
WHERE KPIs_AdminStaff.OBJECTIVE_ID = 2402
AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
,
OBJECTIVE_SCORE =
(
SELECT OBJECTIVE_ID_5
FROM KPIs_AdminStaff_Scores 
WHERE KPIs_AdminStaff.OBJECTIVE_ID = 2409
AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
WHERE
EXISTS
(
SELECT * 
FROM KPIs_AdminStaff_Scores
WHERE  KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
;

score 1 · Accepted Answer · answered Jul 25 '19 at 10:40

You can simplify it by using a CASE statement:

UPDATE KPIs_AdminStaff 
SET OBJECTIVE_SCORE = (
  SELECT 
    CASE KPIs_AdminStaff.OBJECTIVE_ID
      WHEN 2352 THEN KPIs_AdminStaff_Scores.OBJECTIVE_ID_1
      WHEN 2355 THEN KPIs_AdminStaff_Scores.OBJECTIVE_ID_2
      WHEN 2401 THEN KPIs_AdminStaff_Scores.OBJECTIVE_ID_3
      WHEN 2402 THEN KPIs_AdminStaff_Scores.OBJECTIVE_ID_4
      WHEN 2409 THEN KPIs_AdminStaff_Scores.OBJECTIVE_ID_5
    END
  FROM KPIs_AdminStaff_Scores  
  WHERE KPIs_AdminStaff.OBJECTIVE_ID IN (2352, 2355, 2401, 2402, 2409) AND KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
)
WHERE EXISTS (
  SELECT * 
  FROM KPIs_AdminStaff_Scores
  WHERE  KPIs_AdminStaff.EMPLOYEE_NUMBER = KPIs_AdminStaff_Scores.ID
);

Check also if the last WHERE EXISTS(....) is not needed.

**Thank you a lot @forpas.** It worked using the above code. — Milad, Jul 25 '19 at 10:44

Sqlite: Update from multiple columns from another table with more than one conditin

1 Answers1