Question 108320 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Need to update data in a SQL table's column based on combination of values in two different columns

I have a table where we have two columns in question: Emp_ID and Status

Current Table:

Emp_ID Status
12 Permanent
13 Casual
14 Temp
14 Casual
15 Permanent
15 Casual
16 Casual
16 Casual
17 Casual
17 Permanent
18 Causal
18 Casual

Need to create another column in the same table basis the Id and status of the employee.

Status can be: Permanent, Casual, temporary

A given employee can have all three status at the same time.

What i am trying to do is to create another column called Permanent_Status where if the employee has a Permanent or temp status then it flags it to Y whereas if he has a casual status its set to N.

Another caveat to it is that if the employee has both Permanent and casual status or temp and casual status then the flag needs to be set as Y.

its only if the employee has just the casual status then the flag needs to be N.

i am using MySQl for this.

I tried using update table statement but couldn’t figure out the exact logic

Desired Output:

Emp_ID Status Permanent_Status
12 Permanent Y
13 Casual N
14 Temp Y
14 Casual Y
15 Permanent Y
15 Casual Y
16 Casual N
17 Casual Y
17 Permanent Y
18 Casual N
18 Casual N
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up using the following code:

    IF OBJECT_ID(N'tempdb..#EMPLOYEE_DEMOGRAPHICS') IS NOT NULL BEGIN DROP TABLE #EMPLOYEE_DEMOGRAPHICS END;

    select Empl_Class , ID, '2' as Permanent_Status1 into #EMPLOYEE_DEMOGRAPHICS from EMPLOYEE_DEMOGRAPHICS;

    creating temp table putting data for Empl_Class, ID and setting  Permanent_Status1 as 2 for all records

     update #EMPLOYEE_DEMOGRAPHICS
    set Permanent_Status1 = 1 where Empl_Class in ('TMP', 'PER');

    setting Permanent_Status1 = 1 where Empl_Class in ('TMP', 'PER') in temp table

    update #EMPLOYEE_DEMOGRAPHICS set Permanent_Status1 = ( case when Empl_Class = 'CAS' then 'N' when Empl_Class in ('PER', 'TMP') then 'Y'end);

    setting Permanent_Status1 = N when Empl_Class = 'CAS', Empl_Class in ('PER', 'TMP') then 'Y'

    with CTE_rank as( select sub.Empl_Class, sub.ID, sub.Permanent_Status1 from ( select *, rn= ROW_NUMBER () over ( PARTITION by ID order by Permanent_Status1 ) from #EMPLOYEE_DEMOGRAPHICS ) as sub where sub.rn =1) update CTE_rank set Permanent_Status1 = ( case when Empl_Class = 'CAS' then 'N' when Empl_Class in ('PER', 'TMP') then 'Y' end);

    created CTE to ensure we implement the logic where if an employee has Empl_Class in ('PER', 'TMP') then permanent_status is set as Y whereas if his record/records are only CAS then Empl_Class = 'CAS'

    UPDATE EMPLOYEE_DEMOGRAPHICS SET permanent_status = #EMPLOYEE_DEMOGRAPHICS.permanent_status1 FROM EMPLOYEE_DEMOGRAPHICS JOIN #EMPLOYEE_DEMOGRAPHICS ON EMPLOYEE_DEMOGRAPHICS.ID = #EMPLOYEE_DEMOGRAPHICS.ID ;

    updating data for the permanent_status column in EMPLOYEE_DEMOGRAPHICS from temp table by looking up ID column


  2. 0

    You can use below exists clause to achieve your desired result –

    UPDATE your_table T1
  SET Permanent_Status = 'Y'
 WHERE EXISTS (SELECT NULL
                 FROM your_table T2
                WHERE T1.Emp_ID = T2.Emp_ID
                  AND Status IN ('Permanent', 'Temp')
              );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search