Mysql – Merge one table to another

mariadbMySQL

I have a table lets say
Table1 with columns Point_ID, Size, Age, Dist, Treatment
and Table 2 with columns Point_ID, Size, Age, Dist, Treatment, Path, Location.
Table 2 has similar data for Point_ID, Size, Age, Dist, Treatment as Table1 for some values.

I want to merge extra columns of Table 2 in Table1 such that there is no duplication and the resulted table looks likes "Point_ID, Size, Age, Dist, Treatment, Path, Location."
There is data in Table1 in columns Point_ID, Size, Age, Dist, Treatment which does not have path and location, so the path and location should be null for them.
What SQL query to be performed?.

Best Answer

Assuming Point_ID is a primary key or unique key shared by the two tables, then you can do this to create a new table Table3 with the data you specified:

CREATE TABLE Table3 AS 
  SELECT t1.Point_ID, t1.Size, t1.Age, t1.Dist, t1.Treatment, t2.Path, t2.Location
  FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 ON t1.Point_ID = t2.Point_ID
  UNION 
  SELECT Point_ID, Size, Age, Dist, Treatment, Path, Location 
  FROM Table2;

For any row with Point_ID in Table1 that doesn't exist in Table2 the Path and Location columns will be NULL. If it does exist, the Path and Location columns will have the values from Table2. For any row with Point_ID in Table2 that doesn't exist in Table1, the whole row will be added to the new table Table3.

Related Solutions

Mysql – Multiple Similar Tables vs One Master Table

Create the Animal table which has information that all animals share. Add another column to the Animal table called type or something similar. In this, specify whether the animal is a cat, dog, mouse, or whatever.

Then, create Cat, Dog, and Mouse tables that have the Animal ID (The primary key) as a foreign key. Each of those tables has columns specific for Cats, Dogs, and Mice specifically. The Mouse table might have a column Favorite_cheese or some such, which the normal animals table would not. To get the full mouse record, you can join the two tables together.

This allows you to have a relatively lean Animal table while still supporting subclassing functionality.

Mysql – INSERT / UPDATE to merge data, when empty data is an empty string

To avoid putting values two times in your INSERT statement use VALUES(<column_name>) function.

INSERT ... ON DUPLICATE KEY UPDATE Syntax
You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise.

Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL or empty strings you can do

INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
  f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
  l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
  work   = COALESCE(NULLIF(work, ''), VALUES(work));

Here is SQLFiddle demo

If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update

Best Answer

Related Solutions

Mysql – Multiple Similar Tables vs One Master Table

Mysql – INSERT / UPDATE to merge data, when empty data is an empty string

Related Question