Saturday, July 12, 2014

Control flow in MySQL conditional query



MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
Suppose we have user table
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `pwd` varchar(64) NOT NULL,
  `email` varchar(50) NOT NULL,
  `lasttime` date NOT NULL,
  `term` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `user` (`id`, `username`, `pwd`, `email`, `lasttime`, `term`) VALUES
(1, 'admin', '123456', 'admin@example.com', '2014-07-01', 'summer'),
(2, 'jiansen', '123456', 'jiansen@example.com', '2014-02-03', 'spring'),
(3, 'tom', '333333', 'tom@example.com', '2014-10-09', 'winter');

We want to convert lasttime (date format) to spring, summer or winter term.

1) SELECT in control flow in MySQL
SELECT  CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END AS  Term
     FROM user

2) UPDATE in control flow in MySQL
UPDATE  user SET  term=CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END 

Video: Example of control flow to select and update in MySQL

1 comment:

  1. Are you interested in attaining enlightenment and personal growth?

    Try doing the following two yoga exercises daily:

    Sodarshan Kriya Yoga

    Sat Nam Kriya Yoga


    Are you interested in preventing and curing cancer, Alzheimer's, high blood pressure, and many other common diseases using an inexpensive, natural indian spice called turmeric?

    Take two teaspoons full of turmeric powder mixed with a cup of warm soy milk (or any kind of non-dairy milk that you'd like). Also eat 2 or 3 black peppercorns along with it, as the pepper helps your body absorb the turmeric better. You also need fat for the turmeric to be absorbed properly, which is why drinking turmeric mixed in water won't work. You have to drink it with milk or some fatty based liquid. Do this twice a day and you will start to feel amazing within a couple of weeks.


    Lastly, please read the following two ancient indian scriptures which talk about the divine love of God:

    Ananda Vrindavan Campu PDF

    Govinda Lilamrta PDF


    If you have any questions about yoga, meditation, spirituality, or natural cures, feel free to email me at delugar@mail2tor.com

    ReplyDelete