Amlan Amlan - 3 months ago 6
SQL Question

UPDATE TABLE WITH CASE

I have two tables

user_master
and
login_history
.
In
User_master
I want to update the
status
column as A(absent) or P(Present) if user has logged in in current date from login history.
the code I am trying but it updates all the rows. All I want is if the user has logged in , it should match both the tables and update
user_master
status
column as P or A.
Hope My question is clear. Help would be really appreciated. here is my MySQL query

UPDATE User_master a
INNER JOIN
(
SELECT DISTINCT user_name FROM login_history WHERE DATE(`login_time`)=CURRENT_DATE()
) b

SET a.`user_status` = CASE
WHEN a.`user_name`=B.`user_name` THEN 'P'
WHEN a.`user_name`!=B.`user_name` THEN 'A'
END

Answer

Hmmm, I am thinking LEFT JOIN:

UPDATE User_master m
LEFT JOIN Login_History lh
    ON m.user_name = lh.user_name AND
       DATE(lh.login_time) = CURRENT_DATE()
SET m.user_status = (CASE WHEN lh.user_name IS NULL THEN 'A' ELSE 'P' END);

It occurs to me that there might be more than one login on a given date. The result is additional updates on the same row. You can prevent this by doing:

UPDATE User_master m LEFT JOIN
       (SELECT lh.user_name, 'P' as user_status
        FROM Login_History lh
        WHERE lh.login_time >= CURRENT_DATE() AND
              lh.login_tie < DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)
        GROUP BY lh.user_name
       ) lh
       ON m.user_name = lh.user_name 
    SET m.user_status = COALESCE(lh.user_status, 'A');

Notice that I changed the date arithmetic as well. This version should make better use of an index.