带条件的Mysql更新查询

Question

我有一个包含内容的表格。表结构如下。我想用某些条件更新表。我会尝试更新这个，但我不是 MySQL 的新手，请帮助我..

我的表结构:

Emp_id  Emp_Name  Join_date  confirm_date  resign_date  status   con_status
001     arun      2011-01-12 2012-06-12                   A      confirmed 
002     aruna     2011-02-12 2012-11-12    2012-04-10     D      Not-confirmed
002     aruna     2011-06-12 2012-12-12                   A      Not-confirmed
004     vinos     2011-03-12 2012-10-01                   A      //null value

我想在日期时像这样更新 con_status

Emp_id  Emp_Name  Join_date  confirm_date  resign_date  status   con_status
001     arun      2011-01-12 2012-06-12                   A      confirmed 
002     aruna     2011-02-12 2012-11-12    2012-04-10     D      Not-confirmed 
002     aruna     2011-06-12 2012-12-12                   A      Not-confirmed
004     vinos     2011-03-12 2012-10-01                   A      confirmed 

我的情况是这样的。

if(con_status == null)
{
  if(confirm_date > Datetime.Now)and (status='Active')
  {
    //update con_status as confirmed
  }
}
else if(con_status == Not-confirmed)
{
   if(confirm_date > Datetime.Now)and (status='Active')
  {
    //update con_status as confirmed
  }
  else
  {
    maintain the previous status
  }

}

Answer 1

UPDATE my_table 
SET con_status = 'confirmed' 
WHERE status = 'Active' AND NOW() < confirm_date AND (con_status IS NULL OR con_status = 'Not-confirmed')