How can I create an update so that the column REZOBR_INT2 is filled with data with different conditions. It turns out that the last condition works for me and overwrites the first two.

update t_table t set REZOBR_INT2 = '1.Дана Консультация' where ((type_id = 3) and EXISTS (select * from t_block_outboundmess w where date_redirect is null) and conect_id in (1,7) and present_id in (1) and EXISTS (select * from t_block_ger p where regname <> username)) and EXISTS (select * from t_block_ger p where date_end is not null); --commit; update t_table t set REZOBR_INT2 = '2.Рассмотрено обращение' where ((present_id = 2) and EXISTS (select * from t_block_outboundmess w where date_response is not null) and (letter_num is null) and EXISTS (select * from t_block_ger p where date_end is not null)); --commit; update t_table t set REZOBR_INT2 = '3.Заявление удовлетворено' where ((type_id = 2) and (satisf='ДА') and EXISTS (select * from t_block_ger p where date_end is not null)); --commit;
  • 2
    and REZOBR_INT2 not in('1.Дана Консультация', '2.Рассмотрено обращение') ? - 4per
  • It is possible in more detail - dmr
  • 2
    Add this condition to the last query (and similar, but with one value - to the second). It will not allow updating the record updated in previous queries. - Akina

1 answer 1

If formally, then

 update t_table t set REZOBR_INT2 = CASE WHEN ((type_id = 3) and EXISTS (select * from t_block_outboundmess w where date_redirect is null) and conect_id in (1,7) and present_id in (1) and EXISTS (select * from t_block_ger p where regname <> username)) and EXISTS (select * from t_block_ger p where date_end is not null) THEN '1.Дана Консультация' WHEN ((present_id = 2) and EXISTS (select * from t_block_outboundmess w where date_response is not null) and (letter_num is null) and EXISTS (select * from t_block_ger p where date_end is not null)) THEN '2.Рассмотрено обращение' WHEN ((type_id = 2) and (satisf='ДА') and EXISTS (select * from t_block_ger p where date_end is not null)) THEN '3.Заявление удовлетворено' ELSE REZOBR_INT2 END;
  • and case if stumbles upon a fulfilled condition the rest does not check? - 4per
  • one
    @ 4per That's right. Finds a return case expression return case. - Akina
  • At the end of the sample put ELSE 'Not known' END; - dmr
  • Found an error, this condition is not met and EXISTS (select * from t_block_ger p where regname <> username)) Ie I have a regname column from the t_block_ger table, and a username column from the t_table table. How can I fix it? - dmr
  • one
    like p alias I only have table t_block_ger. Yeah ... only those tables 4 copies - that each copy should have its own, unique alias. Even if they do not have a common scope, everything is calmer ... aliases in such expressions do not show the expected result. There is no. If a table is specified, the field is taken from it, with no options. If from this place this table is invisible, there will be an unknown token error or something like that. - Akina