Updating a table using CASE and conditions (postgresql)
You are doing wrong.
Use Case Statement witin a Update Statement instead of using Update Statement Within Case Statement.
Like
Update **Table**
Set **Col1**=
Case when **Col10=1** then 5
else case when **Col10=2** THEN 6
**ELSE 10** END
**ELSE 15** END
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 23, 2022Comments
-
Admin about 2 years
i have the tables ::
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-->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) );
INSERTED DATA IS
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
(6 rows)
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)
Query :
I want a approval table to be updated
CREATE TABLE approval ( eid integer, lid integer, asid integer, bal integer );
as the output
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)
conditions:: i tried this query for getting the sid into approval table as stated below::
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
i want even the balance column should be updated basing on sid verification i.e..,
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
Is there a way to solve this?
finally i want to see all the employee leaves which are approved and who approved it and there balance leaves left!!
Full details are in the [SQL FIDDLE] (http://sqlfiddle.com/#!12/3e6a7/18)