CODEWITHSUNDEEP
sqlpostgresql
kris
kris

Reputation: 129

insert data from another table where ids match

I want to insert some data from one table into another.

For example, I have a table a.user and b.user (2 tables in different schemas)

Both tables have columns id where some of the ids match. For the matching ids, I want to insert some data from a.user.userSettings into b.user.userSettings.

I am able to do a simple select + inner join to get the userSettings where ids from both tables match.

Now,I tried inserting this into b.users, and it is not working.

Here is my query:

insert into b.user ("userSettings") select "userSettings" from a.user
inner join b.user
on a.user.id = b.users.id

I am getting the following error:

SQL Error [23502]: ERROR: null value in column "username" violates not-null constraint

I don't understand the error, and why it is complaining about the column username when I am not trying to insert anything into that column

Upvotes: 0

Views: 3971

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

It sounds like you want to update rows in b, not insert them:

update b.user b
    set userSettings = a.userSettings
    from a.user a
    where a.id = b.id;

Upvotes: 6

Related Questions