0

I have two tables as below.

Tabel A:

        ResultID(PK) | ImportDate | Comment1
        -------------------------------------
        101          | 25-09-2019 | One
        --------------------------------------
        102          | 25-09-2019 | Two
        --------------------------------------
        103          | 25-09-2019 | Three
        ----------------------------------------

Table B: 

        ResultID(PK) | ImportDate | Comment2
        -------------------------------------
        101          | 26-09-2019 | new one
        --------------------------------------
        104          | 26-09-2019 | four
        --------------------------------------

So the output should look like

Table A:

ResultID(PK) | ImportDate | Comment1
-------------------------------------
101          | 26-09-2019 | new one
--------------------------------------
102          | 25-09-2019 | Two
--------------------------------------
103          | 25-09-2019 | Three
--------------------------------------
104          | 26-09-2019 | four
--------------------------------------

Question: I want to get resulting Table A as mentioned above if ResultID is matched between Table A and Table B, I want to update all column in Table A from Table B for that ResultID. If ResultID from Table B is not present in Table A, insert it to Table A.

What I have tried in MySQL: 

UPDATE TableA 
SET comment1=
(SELECT comment2 FROM TableB WHERE TableA.ResultId=TableB.ResultId);

Above solution only works for one column to update Table A. I also tried with multiple column update reference from SQL Server 2005 implementation of MySQL REPLACE INTO? but multiple column update is not working for my scenarios.

For my real scenarios- I have 40 columns and 50,000 rows.

Could you please provide me any hint or solution? Thank you.

Jayveer Parmar
  • 500
  • 7
  • 25
  • 2
    I think you are trying to get away from including the 40 columns, but unless you are doing dynamic sql you are out of luck. Just add the 40 columns, using one of the solutions shown below. – TomC Sep 26 '19 at 01:48

1 Answers1

1

Since you have a primary key on ResultID, you can simply use an INSERT ... ON DUPLICATE KEY UPDATE query to transfer all the data from TableB to TableA:

INSERT INTO TableA (ResultID, ImportDate, Comment1)
SELECT ResultID, ImportDate, Comment2 FROM TableB
ON DUPLICATE KEY UPDATE
  ImportDate = VALUES(ImportDate),
  Comment1 = VALUES(Comment1);

Output:

ResultID    ImportDate  Comment1
101         26-09-2019  new one
102         25-09-2019  Two
103         25-09-2019  Three
104         26-09-2019  four

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95