根据ID列表为多个记录插入相同的值

I have a PostgreSQL database and want to insert the same value for multiple records based on record IDs I have.
Is there a way to make a WHERE condition in the INSERT statement? For example:

我有一个PostgreSQL数据库,并希望根据我的记录ID为多个记录插入相同的值。有没有办法在INSERT语句中创建WHERE条件?例如:

insert into Customers (new-customer) values ('t') where customer_id in (list)

2 个解决方案

#1

Yes, you can do something like:

是的,你可以这样做:

INSERT INTO customers(customer_id, customer_name)
    select  13524, 'a customer name'
where  13524 = ANY ('{13524,5578,79654,5920}'::BIGINT[])

Here, a customer with id: 13524 will be added because it's ID is in the list: {13524,5578,79654,5920}

在这里,将添加ID为13524的客户,因为它的ID在列表中:{13524,5578,79654,5920}

I hope that what you are looking for!

我希望你在找什么!

#2

To insert a row for every id in your list, you can use unnest() to produce a set of rows:

要为列表中的每个id插入一行,可以使用unnest()生成一组行:

INSERT INTO customers(customer_id, column1)
SELECT id, 't'
FROM   unnest ('{123,456,789}'::int[]) id;

If you misspoke and actually meant to UPDATE existing rows:

如果你错过了并且实际上意味着更新现有行:

UPDATE customers
SET    column1 = 't'
WHERE  customer_id = ANY ('{123,456,789}'::int[]);

#1