How to update top N records for a table in PostgreSQL?
Dung Do Tien
Dec 31 2020
334
Hello, I have a [articlevote
] table to help store the data vote of the user, it's related to some articles. That table look as below :
CREATE TABLE public.articlevote (
id SERIAL,
answerid INTEGER ,
questionid INTEGER,
createdby UUID,
createddate TIMESTAMP
)
Now I want to update top 3000 records in this table, set answerid
column value from 12 to 14.
UPDATE
command only filter and update base on WHERE
command. it does not allow TOP
command. Please support me if you have any solutions.
Have 1 answer(s) found.
-
M1
Mary Christ Dec 31 2020
You can use
WITH
command to help return a data set and you can update only on this data set. See an example below:WITH cte AS ( select * from articlevote where answerid = 12 limit 3000 ) update articlevote set answerid = 14 where id in ( select id from cte );
I hope it's helpful for you.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.