I am trying to update multiple rows in a table by using the following statement:
update test as t set column_a = c.column_a from (values ('123', 1), ('345', 2) ) as c(column_b, column_a) where c.column_b = t.column_b;
However, in my database the values in column_b are not unique (e.g., multiple rows can have the '123'). I also have a column_c with a DATE type. For each of the rows in the update statement, I only want the update stated above to happen on the row with the most recent date in column_c, for example by ordering the data by date and using LIMIT 1.
Therefore, I am trying to combine this query with the answer provided here. However, I have trouble doing this.