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

Mysql – Merge tables with extra columns, filling missing values with nulls

I have two tables, table1 and table2. Both use the same primary key of ID and I’ve made sure there’s no overlap between the IDs so they can be merged together. Both the tables are almost identical in structure, apart from a few changes to table2:

  • The name column has been added
  • The postcode column has been moved from the end to immediately after name
  • The purchased column has been renamed to product

table1 therefore has 14 columns and table2 has 15.

table1 table2
id id
payment_id payment_id
payment_gateway payment_gateway
gateway_payment_id gateway_payment_id
payment_method payment_method
payment_status payment_status
amount amount
currency currency
email email
name
postcode
purchased product
manufacturer manufacturer
postcode
created_at updated_at
updated_at updated_at

Is it possible to merge table2 into table1 – or merge them both into a new table, if easier – while filling NULLS into the missing values of name for table1?

My SQL knowledge isn’t great so the closest I’ve come is this:

INSERT INTO table1
SELECT id,
       payment_id,
       payment_gateway,
       gateway_payment_id,
       payment_method,
       payment_status,
       amount,
       currency,
       email,
       name,
       postcode,
       product,
       manufacturer,
       created_at,
       updated_at
  FROM table2;

But this fails with:

Error Code: 1136. Column count doesn’t match value count at row 1

Neither of the proposed duplicates are duplicates of this question because:

  • None of the answers in them actually answer my question without serious rewriting of their code (which would make them completely different answers for completely different, non-duplicate questions)

  • None of them contain my requirements around moving and renaming of columns.

A question simply containing the same error code string does not make it the same question, there are countless reasons the same error might be triggered. None of the proposed answers are duplicates because none of them actually answer the question I’m asking.

Tags:

2

Answers


  1. 0

    You can simply union all two tables by adding nulls in the missing places and then insert in a third table.

    Login or Signup to reply.
  2. 0

    You need to specify the columns you are filling, like:

    INSERT INTO table1(
       id,
       payment_id,
       payment_gateway,
 ........... , -- I am not copying/pasting all the column names here, tooo afraid that I am going to make a mistake with it 😉
       created_at,
       updated_at
       )
SELECT id,
       payment_id,
       payment_gateway,
       gateway_payment_id,
       payment_method,
       payment_status,
       amount,
       currency,
       email,
       name,
       postcode,
       product,
       manufacturer,
       created_at,
       updated_at
  FROM table2;

    See docs of the INSERT INTO statement, where it says col_name

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

Back To Top
Search