Styjun

Why did Windows 95 crash the whole system but newer Windows only crashed programs?

Is it okay for me to decline a project on ethical grounds?

Was Donald Trump at ground zero helping out on 9-11?

How did astronauts using rovers tell direction without compasses on the Moon?

How can I solve this sudoku?

Why are prop blades not shaped like household fan blades?

How do I make my photos have more impact?

How to efficiently shred a lot of cabbage?

How to choose using Collection<Id> rather than Collection<String>, or the opposite?

Using Python in a Bash Script

How to foreshadow to avoid a 'deus ex machina'-construction

Why didn't Stark and Nebula use jump points with their ship to go back to Earth?

Is Ear Protection Necessary For General Aviation Airplanes?

Can a US President, after impeachment and removal, be re-elected or re-appointed?

Typesetting numbers above, below, left, and right of a symbol

Should 2FA be enabled on service accounts?

Word for soundtrack music which is part of the action of the movie

Spider-Man and Fantastic 4 crossover comic with Double Identity Scene

Would people understand me speaking German all over Europe?

Can machine learning learn a function like finding maximum from a list?

Why put copper in between battery contacts and clamps?

If the Moon were impacted by a suitably sized meteor, how long would it take to impact the Earth?

What Marvel character has this 'W' symbol?

How can Paypal know my card is being used in another account?

How to do an upsert when updating a table from another table postgres?

How to select the nth row in a SQL database table?SQL update from one Table to another based on a ID matchSQLite - UPSERT *not* INSERT or REPLACEHow to 'insert if not exists' in MySQL?How do I UPDATE from a SELECT in SQL Server?What are the options for storing hierarchical data in a relational database?How to select rows with no matching entry in another table?SQL select only rows with max value on a columnHow to exit from PostgreSQL command line utility: psqlHow to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have table A that contains about 10 millions rows and Table B which contains some updated information for some rows in table A and also contains new rows that don't exist in table A.

I want to update table A using table B and at the same time insert rows that have no match in tableA.

I found many answers like the solution below but it seems like they all miss the inserting part that I'm looking for.

UPDATE A 
SET code = B.code
FROM B
WHERE A.id = B.id 

sql postgresql

share|improve this question

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the data is in two tables, just run two queries, an update and an insert.
  
  – Gordon Linoff
  Mar 26 at 21:47
  

  
  
  
  

add a comment | 

0

I have table A that contains about 10 millions rows and Table B which contains some updated information for some rows in table A and also contains new rows that don't exist in table A.

I want to update table A using table B and at the same time insert rows that have no match in tableA.

I found many answers like the solution below but it seems like they all miss the inserting part that I'm looking for.

UPDATE A 
SET code = B.code
FROM B
WHERE A.id = B.id 

sql postgresql

share|improve this question

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the data is in two tables, just run two queries, an update and an insert.
  
  – Gordon Linoff
  Mar 26 at 21:47
  

  
  
  
  

add a comment | 

I have table A that contains about 10 millions rows and Table B which contains some updated information for some rows in table A and also contains new rows that don't exist in table A.

I want to update table A using table B and at the same time insert rows that have no match in tableA.

I found many answers like the solution below but it seems like they all miss the inserting part that I'm looking for.

UPDATE A 
SET code = B.code
FROM B
WHERE A.id = B.id 

sql postgresql

share|improve this question

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

UPDATE A 
SET code = B.code
FROM B
WHERE A.id = B.id 

share|improve this question

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

share|improve this question

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

asked Mar 26 at 21:40

tito.300tito.300

11199 bronze badges

1 Answer
1

active

oldest

votes

1

Use two queries:

update a
 set code = b.code
 from b
 where a.id = b.id;

insert into a (id, code)
 select id, code
 from b
 where not exists (select 1 from a where a.id = b.id);

You can also use on conflict

insert into a (id, code)
 select b.id, b.code
 on conflict on constraint a_id
 do update set code = b.code;

share|improve this answer

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55366623%2fhow-to-do-an-upsert-when-updating-a-table-from-another-table-postgres%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Use two queries:

update a
 set code = b.code
 from b
 where a.id = b.id;

insert into a (id, code)
 select id, code
 from b
 where not exists (select 1 from a where a.id = b.id);

You can also use on conflict

insert into a (id, code)
 select b.id, b.code
 on conflict on constraint a_id
 do update set code = b.code;

share|improve this answer

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

add a comment | 

1

Use two queries:

update a
 set code = b.code
 from b
 where a.id = b.id;

insert into a (id, code)
 select id, code
 from b
 where not exists (select 1 from a where a.id = b.id);

You can also use on conflict

insert into a (id, code)
 select b.id, b.code
 on conflict on constraint a_id
 do update set code = b.code;

share|improve this answer

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

add a comment | 

Use two queries:

update a
 set code = b.code
 from b
 where a.id = b.id;

insert into a (id, code)
 select id, code
 from b
 where not exists (select 1 from a where a.id = b.id);

You can also use on conflict

insert into a (id, code)
 select b.id, b.code
 on conflict on constraint a_id
 do update set code = b.code;

share|improve this answer

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

update a
 set code = b.code
 from b
 where a.id = b.id;

insert into a (id, code)
 select id, code
 from b
 where not exists (select 1 from a where a.id = b.id);

insert into a (id, code)
 select b.id, b.code
 on conflict on constraint a_id
 do update set code = b.code;

share|improve this answer

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

answered Mar 26 at 21:48

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges