0

For example I have this kind of table

Table A

id   col1   col2
1    aaa    bbb

Table A_STG

stg_id   id   col1   col2
1        1    xxx    yyy

how can I update Table A value based on column "id" with value from Table A_STG without mapping SET t1.col1 = t2.col1 and so on.

so the final result will be:

Table A

id   col1   col2
1    xxx    yyy

Thank you before.

Table A and A_STG have similar structure, the only difference is Table A_STG have "stg_id".

btw Table A.id is auto increment.

Mr.Rendezvous
  • 1,933
  • 5
  • 20
  • 34
  • Does this answer your question? [MySQL - UPDATE query based on SELECT Query](https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) – slashroot Nov 21 '21 at 08:40
  • @slashroot that one still mapping one by one right?? btw I update a bit – Mr.Rendezvous Nov 21 '21 at 09:14
  • 1
    Well if your trying to update values in one table based on values from a second table, there has to be a correlation between these 2 tables. Or else how will it know what to update. So in your question you want to update values in **TableA** with values from **TableA_STG** without mapping **t1.col1** and **t2.col1**, but what connects them both? Could we use **t1.id = t2.id** (Are those 2 ids related?) – slashroot Nov 21 '21 at 09:26
  • @slashroot yes the correlation is that one t1.id and t2.id, not related by FK it's free – Mr.Rendezvous Nov 21 '21 at 09:31

1 Answers1

0

So just like in the link I put in my first comment you are looking for something like this

UPDATE TableA t1
INNER JOIN TableA_STG t2 ON t1.id = t2.id
SET t1.col1 = t2.col1, t1.col2 = t2.col2
-- Can also add a WHERE clause if needed
slashroot
  • 773
  • 1
  • 4
  • 13
  • is there's a way without doing mapping SET? since that's only example, reality is there's a lot of column, and for many tables – Mr.Rendezvous Nov 21 '21 at 10:02
  • @Mr.Rendezvous Unfortunately when running the `UPDATE` operation you must declare the `SET` assignment list. Because yes even though you are updating a row, its the columns within that row that get updated. You can read more about the [UPDATE STATEMENT HERE](https://dev.mysql.com/doc/refman/8.0/en/update.html) – slashroot Nov 21 '21 at 10:12