Postgres error when updating row with value from another table

Question

I've being trying to update a row with a value from another table but I keep getting the same error:

table name "chats" specified more than once

I'm trying to insert a row into the messages table, and then use that timestamp (message_timestamp) to update the last_updated field in the chats table.

Any help would be appreciated! Been stuck on this for a day now:

WITH result AS 
(INSERT INTO messages (user_id, chat_id, message_timestamp, users_read, message_text) 
VALUES ($1, $2, NOW(), '{}', $3) RETURNING message_timestamp, chat_id)

UPDATE chats SET chats.last_updated=result.message_timestamp FROM result, chats WHERE chats.id=result.chat_id;

Edit:

On the other hand, removing chats from my FROM clause as so:

UPDATE chats SET chats.last_updated=result.message_timestamp FROM result WHERE chats.id=result.chat_id;

results in a different error:

column "chats" of relation "chats" does not exist

Which is weird considering I never call chats.chats

Edit 2: Create statement for the chat table:

CREATE TABLE IF NOT EXISTS chats (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  chat_name VARCHAR,
  last_message TIMESTAMP NOT NULL
);

hey @Adrian Coutsoftides : Can you share chats table schema or the create statement? — Abhishek
– Abhishek, Commented Jan 2, 2021 at 14:28
UPDATE chats SET last_updated=result.message_timestamp FROM result WHERE chats.id=result.chat_id; — Abhishek
– Abhishek, Commented Jan 2, 2021 at 14:48

forpas · Accepted Answer · 2021-01-02 14:49:10Z

1

From UPDATE:

Do not include the table's name in the specification of a target column

So the column last_updated that you want to update should not be qualified with the table's name like chats.last_updated:

UPDATE chats 
SET last_updated = result.message_timestamp 
FROM result 
WHERE chats.id = result.chat_id;

See a simplified demo.

answered Jan 2, 2021 at 14:49

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Coutsoftides Over a year ago

Damn; that's weird considering all other queries let you specify the column name to disambiguate the request. Guess now I know!

Collectives™ on Stack Overflow

Postgres error when updating row with value from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related