Postgres - Move the records from one table to another based on condition

Question

I have two tables with millions of rows in postgres. I wanted to move records from one table to another.

Table name - event
List of columns:

"id"
"url"
"parent_id"
"created"
"created_by"
"last_modified"
"last_modified_by"
"tags"
"type"
"sub_type"
"category"
"name"
"preference"

Table name - preference
List of columns:

"id" - If the entity_type is event, id column in the event table goes here
"entity_type" // event or slot.
"preference"
"created"
"created_by"
"last_modified"
"last_modified_by"
"parent_entity_id"

And these tables are already populated with millions of data. And I have added a new column named preference in the event table, which does not have any data. Now, I wanted to move the

preference table preference column data to the event table preference column.

Condition:
Migrating the data from the preference column (In preference table) to the preference column (In event table) only when the entity_type is an event. And the insert should happen with the corresponding row in the event table.

I tried this query, but this seems to be incorrect. And Postgres throws a syntax error.

insert into <schema_name>.event a (preference) where a.id = '<eventId goes here>' (select preference from <schema_name>.preference where entity_id = '<eventId goes here>'
and entity_type = 'event')

Answer 1

You should use update instead of insert, because the table is already populated with data.

update event e
set preference = (select preference from preference p where p.entity_id = e.id and entity_type = 'event')