0

I have a pretty standard table as following:

create table A (
    id bigint(20) primary key not null auto_increment,
    external_id varchar(64) not null unique,
    some_data varchar(64),
    ... other columns, not important here ...
);

Now I have another table, which is basically a subset of A:

create table B (
    id bigint(20) primary key not null auto_increment,
    external_id varchar(64) not null unique,
    some_data varchar(64) not null,
);

My goal is to move all rows from B to A for which there is a corresponding row with equal external_id value. Here, some_data is simply overwritten; that is, the value from the row in B is taken as final value for the matching row in A.

In other words, we only update rows in A, never insert new ones; there must be a match. Also, if we were table to find a match between B and A, the row in B is deleted as well.

You can consider table B as some kind of "holding table" which holds values until the corresponding row in A becomes inserted.

What is the best query or approach to do this?

The related answers do not really answer my question completely, as these first update A and then delete from B. Doesn't this leave us with the tiny possibility that we are deleting rows from B which were "just" updated/inserted, and thus were not covered by the initial update A query?

Koen
  • 1
  • 1
  • Only you know if there could be any updates between the sync and delete. However, this concern was not mentioned when you asked your question, so the duplicates answer your question as it was at the time of the closure. If you have a follow-up question, then please ask it separately in another question! – Shadow Feb 01 '23 at 20:19

0 Answers0