Mysql update query with condition

Question

i have a table with content. The table structure is below. I want to update the table with some condition. I will try to update this but i can't am new for MySQL please help me..

My table structure :

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

I want to update the con_status like this when the date

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

My condition like this.

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
  }

}

adam · Accepted Answer · 2012-10-19 11:58:46Z

2

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

answered Oct 19, 2012 at 11:58

adam

211 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

PyQL · Accepted Answer · 2012-10-19 11:21:04Z

0

update table
set con_status='confirmed'
where
status='Active' and now()<confirm_date and  con_status is null

update table
set con_status='confirmed'
where
status='Active' and now()<confirm_date and  con_status ='Not-confirmed'

answered Oct 19, 2012 at 11:21

PyQL

1,8303 gold badges18 silver badges23 bronze badges

Collectives™ on Stack Overflow

Mysql update query with condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related