title: Updating a table using CASE and conditions (postgresql)
body: <p><strong>i have the tables ::</strong></p>
  
  <pre><code>CREATE TABLE emp1
  (
    eid integer NOT NULL,
    ename character varying(20),
    sid integer,
    ssid integer,
    CONSTRAINT pk_eid PRIMARY KEY (eid)
  );
  
  CREATE TABLE leave_type
  (
    eid integer,
    lid integer,
    lnum integer,
    emp_bal integer,
    sno serial NOT NULL,
    CONSTRAINT pk_sno PRIMARY KEY (sno),
    CONSTRAINT fk_eid FOREIGN KEY (eid)
        REFERENCES emp1 (eid) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  );//emp_bal--&gt;employee balance leaves which is considered as 8
  
  CREATE TABLE result
  (
    eid integer,
    lid integer,
    sd date,
    ed date,
    sida boolean,
    ssida boolean,
    rsno serial NOT NULL,
    CONSTRAINT pk_rsno PRIMARY KEY (rsno)
  );
  </code></pre>
  
  <p><strong>INSERTED DATA IS</strong></p>
  
  <pre><code>emp1
  -----
   eid | ename | sid | ssid
  -----+-------+-----+------
     1 | a     |   2 |    8
     3 | c     |   4 |    9
     2 | b     |   3 |    8
     4 | d     |   2 |    8
     5 | e     |   2 |    8
     6 | f     |   4 |    9
  </code></pre>
  
  <p>(6 rows)</p>
  
  <pre><code>leave_type
  ----------
   eid | lid | lnum | emp_bal | sno
  -----+-----+------+---------+-----
     1 |   0 |    1 |       8 |   1
     3 |   0 |    1 |       8 |   2
     5 |   0 |    1 |       8 |   3
     1 |   1 |    1 |       8 |   4
     1 |   2 |    2 |       8 |   5
  (5 rows)
  
  result
  -------
   eid | lid |     sd     |     ed     | sida | ssida | rsno
  -----+-----+------------+------------+------+-------+------
     1 |   0 | 2013-01-01 | 2013-01-01 | t    | f     |    1
     3 |   0 | 2013-01-09 | 2013-01-09 | t    | f     |    2
     5 |   0 | 2013-01-11 | 2013-01-11 | t    | f     |    3
     1 |   1 | 2013-02-14 | 2013-02-14 | t    | f     |    4
     1 |   2 | 2013-03-15 | 2013-03-16 | f    | t     |    5
  (5 rows)
  </code></pre>
  
  <p><strong>Query :</strong></p>
  
  <p>I want a approval table to be updated  </p>
  
  <pre><code>CREATE TABLE approval
      (
        eid integer,
        lid integer,
        asid integer,
        bal integer
      );
  </code></pre>
  
  <p>as the output</p>
  
  <pre><code>      eid | lid | sid |bal
       -----+-----+---+--
         1 |   0 | 2 | 7
         3 |   0 | 4 | 7
         5 |   0 | 2 | 7
         1 |   1 | 2 | 6
         1 |   2 | 8 | 4
      (5 rows)
  </code></pre>
  
  <p><strong>conditions::</strong>
  i tried this query for getting the sid into approval table as stated below::</p>
  
  <pre><code>CASE WHEN r.sida='t' 
    THEN (update approval set a.asid=e.sid where a.eid=e.eid from emp1 e,approval a)
  WHEN r.ssida='t'
    THEN (update approval set a.asid=e.ssid where a.eid=e.eid from emp1 e,approval a) 
  ELSE 0
  END
  </code></pre>
  
  <p>i want even the balance column should be updated basing on sid verification i.e..,</p>
  
  <blockquote>
    <p>CASE if r.sida='t' then bal=emp1.emp_bal-1 and emp_bal in emp1 should
    be updated to latest value of bal from approval else if r.ssida='t'
    then bal=emp_bal-2 and emp_bal in emp1 should be updated to latest
    value of bal from approval</p>
  </blockquote>
  
  <p>Is there a way to solve this?</p>
  
  <p>finally i want to see all the employee leaves which are approved and who approved it and there balance leaves left!!</p>
  
  <p>Full details are in the [SQL FIDDLE] (<a href="http://sqlfiddle.com/#!12/3e6a7/18" rel="noreferrer">http://sqlfiddle.com/#!12/3e6a7/18</a>) </p>