0

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?.

0

1 Answer 1

1

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.

2
  • That does not seem to work. Is there any other way?. The columns from table 2 were not added to this new table. Dec 25, 2017 at 15:11
  • I've modified the query a bit so that it includes rows in Table2 not found in Table1. That should at least help some.
    – dbdemon
    Dec 25, 2017 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.