I want update multiple row with multiple condition.
Here is my select query to test result.
select name, register_date, expired_date, to_char(register_date + interval '1 year' - interval '1 day', 'YYYY-MM-DD') from tb_agent where agent_id = agent_id and agent_id in (46,47,62)
It can show result like I expected,
But when I craft to multiple update with this code
update tb_agent set expired_date = (select to_char(register_date + interval '1 year' - interval '1 day', 'YYYY-MM-DD') from tb_agent where agent_id = agent_id) where agent_id in (46,47,62)
I got this
column "expired_date" is of type date but expression is of type text
register_date is date time column and expired_date is varchar
Is something wrong with my code?
Thanks, in advance.
to_char converts the date to a formatted string. you can cast it instead as a date.
but the update statement could be simplified to the following as well:
update tb_agent
set expired_date = (register_date + interval '1 year' - interval '1 day')::date
where agent_id in (46,47,62)