Rafik malek Rafik malek - 6 months ago 8
SQL Question

update and select query together to update table data from same table

I having a table given in image wich having columns a b c d thats stored in column ans i want to update the table's conclusion column with ans columns value. i make a select query to get the value of a b c or d which is stored in ans column .but now i am confuse how to update that value in conclusion with update and select together.

select CASE WHEN (SELECT ans FROM `test` q1 WHERE `ans`!='' AND q1.id=q2.id)='A' THEN A ELSE CASE WHEN (SELECT ans FROM `test` q1 WHERE `ans`!='' AND q1.id=q2.id)='B' THEN B ELSE CASE WHEN (SELECT ans FROM `test` q1 WHERE `ans`!='' AND q1.id=q2.id)='C' THEN C ELSE D END END END as data from test q2


enter image description here


Answer

I think you simply need this

Update Test 
set conclusion = CASE ans    
                    WHEN 'A' THEN A  
                    WHEN 'B' THEN B  
                    WHEN 'C' THEN C 
                     ELSE D 
                  END