Question 243793 in Mysql Question posted in
The official MySQL documentation can be found here.

How to copy the contents of several columns from Table B to Table A the same row exists in both tables in mySQL?

Okay, a brief background. I have been working on a project using a mySQL database. Originally, I had tasks data stored in two different tables (Table A and Table B). However, as time progressed, I found that this design made SQL queries more complicated than necessary. So I increased the number of columns in Table A so that it could store all the tasks data in one single table. After the change, Table B is no longer used to store new data.

However, I would like to copy old data from Table B back into Table A, so I do not need to scrap both tables and start from scratch.

Suppose Table A has this structure (columns "start", "end", "admin", "worker" and "progress" were added later):

ID | name | reference | start | end | admin | worker | progress | completed |

Suppose table B has this structure

ID | start | end | admin | worker | progress |

How do I copy data from Table B to the corresponding columns of Table A when rows with the same ID exist in both tables?

Tags:

2

Answers


  1. 0

    You can simply use a series of individual UPDATE statements for each column.

    UPDATE TableA
JOIN TableB ON TableA.ID = TableB.ID
SET 
    TableA.start = TableB.start,
    TableA.end = TableB.end,
    TableA.admin = TableB.admin,
    TableA.worker = TableB.worker,
    TableA.progress = TableB.progress;
    Login or Signup to reply.
  2. 0

    You can copy data from Table B to Table A for rows with the same ID using an SQL UPDATE statement with a JOIN clause.

    UPDATE TableA AS A
JOIN TableB AS B ON A.ID = B.ID
SET
    A.start = B.start,
    A.end = B.end,
    A.admin = B.admin,
    A.worker = B.worker,
    A.progress = B.progress;

    This query updates the columns in Table A (start, end, admin, worker, and progress) with the corresponding values from Table B where the "ID" matches in both tables.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search