0

I'm trying to update a table that is linked to another table through an array type column. My question is, is it possible to run a join during an update in Postgres?

Here's what I have:

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.table_a (
    id serial PRIMARY KEY,
    table_b_ids BIGINT[] NOT NULL,
    table_a_name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS my_schema.table_b (
    id serial PRIMARY KEY,
    tag_value BIGINT UNIQUE NOT NULL
);

INSERT INTO my_schema.table_b VALUES
    (1, 12345),
    (2, 12346);

INSERT INTO my_schema.table_a VALUES
    (1, '{1}', 'Michael');

I need to be able to update table_a like this:

UPDATE my_schema.table_a
SET table_a_name = 'John'
WHERE 12345 = ANY(table_b_ids)
RETURNING *

Instead of,

. . .
WHERE 1 = ANY(table_b_ids)
. . .

Is this possible?

Fiddle

Mike K
  • 7,621
  • 14
  • 60
  • 120
  • [`update my_schema.table_a a set table_a_name = 'John' from my_schema.table_b b where b.id=any(a.table_b_ids) and b.id=1 returning *;`](https://dbfiddle.uk/mWupU0EH) – Zegarek Feb 10 '23 at 09:20

0 Answers0