How to add data into the column with null values from another table?

Question

I'm trying to populate my table with data gathered.

I have three table, INCIDENT_MAIN, INCIDENT_REPORT and PROJECT_PROFILE.

I have to fill the PROJECT_PROFILE table from the data provided by INCIDENT_MAIN AND INCIDENT_REPORT.

here is my query for doing INNER JOIN

INSERT INTO PROJECT_PROFILE (INCIDENT_REPORT_ID, PROJECT_NAME,PROJECT_TYPE, PROJECT_PHASE, PROJECT_START_DATE,PROJECT_END_DATE,DOMESTIC_INTERNATIONAL,PROJECT_FACILITIES_LOGISTIC)
  SELECT PROJECT_NAME, PROJECT_TYPE, PROJECT_PHASE, PROJECT_START_DATE, ESTIMATED_PROJECT_END_DATE, DOMESTIC_INTERNATIONAL, PROJECT_FACILITIES_LOGISTIC
  FROM INCIDENT_MAIN INCIDENT_REPORT_ID
  INNER JOIN INCIDENT_REPORT ON INCIDENT_REPORT.INCIDENT_REPORT_ID = PROJECT_PROFILE.INCIDENT_REPORT_ID

I got the error message of multi-part identifier 'PROJECT_PROFILE.INCIDENT_REPORT_ID'could not be bound

So I decide to populate the PROJECT_PROFILE table with INCIDENT_REPORT_ID first so that I can collect data which is INCIDENT_REPORT_ID from the INCIDENT_REPORT table. The PROJECT_PROFILE_ID is auto increment since I set it as IDENTITY KEY.

The data is inserted just fine. Below is the result.

234 rows affected.

Now, I try to replace the NULL values with the data from INCIDENT_MAIN

Here is the query

INSERT INTO PROJECT_PROFILE (PROJECT_NAME,PROJECT_TYPE, PROJECT_PHASE, PROJECT_START_DATE,PROJECT_END_DATE,DOMESTIC_INTERNATIONAL,PROJECT_FACILITIES_LOGISTIC)
  SELECT PROJECT_NAME, PROJECT_TYPE, PROJECT_PHASE, PROJECT_START_DATE, ESTIMATED_PROJECT_END_DATE, DOMESTIC_INTERNATIONAL, PROJECT_FACILITIES_LOGISTIC
  FROM INCIDENT_MAIN
  WHERE PROJECT_PROFILE_ID >='1'

But now, the data form INCIDENT_MAIN is inserted under the last row of 234 which makes the PROJECT_PROFILE_ID increased and the NULL VALUES ARE NOT REPLACED.

What is the right way to do the query so that the NULL values can be replaced with real data and there's no increment in PROJECT_PROFILE_ID.

EDITED

So, I tried this update query as per suggestion

UPDATE PROJECT_PROFILE
SET PROJECT_PROFILE.PROJECT_NAME = INCIDENT_MAIN.PROJECT_NAME,
PROJECT_PROFILE.PROJECT_PHASE = INCIDENT_MAIN.PROJECT_PHASE,
PROJECT_PROFILE.PROJECT_START_DATE = INCIDENT_MAIN.PROJECT_START_DATE,
PROJECT_PROFILE.PROJECT_END_DATE = INCIDENT_MAIN.ESTIMATED_PROJECT_END_DATE,
PROJECT_PROFILE.DOMESTIC_INTERNATIONAL = INCIDENT_MAIN.DOMESTIC_INTERNATIONAL,
PROJECT_PROFILE.PROJECT_FACILITY_LOGISTIC = INCIDENT_MAIN.PROJECT_FACILITIES_LOGISTIC
FROM PROJECT_PROFILE JOIN INCIDENT_MAIN ON
PROJECT_PROFILE.PROJECT_NAME= INCIDENT_MAIN.PROJECT_NAME
WHERE PROJECT_PROFILE.PROJECT_PROFILE_ID <='234'

getting (0 rows affected)

before you execute the last query, there are data already in the table from earlier INSERT statement. If these are not what you wanted, delete it and re-execute the correct INSERT statement — Squirrel
– Squirrel, Commented Apr 23, 2019 at 8:04
The error in your first query on PROJECT_PROFILE.INCIDENT_REPORT_ID is because the query specified PROJECT_PROFILE but this table name or alias is not found in your query FROM clause at all — Squirrel
– Squirrel, Commented Apr 23, 2019 at 8:09
@Squirrel It is not what I want, I want to replace the null values with the real data from another table, but haven't found the right way to do it hence the question. Sorry if it's hard to understand. — WanHazyan
– WanHazyan, Commented Apr 23, 2019 at 8:09
You can use a UPDATE query FROM PROJECT_PROFILE JOIN INCIDENT_MAIN — Squirrel
– Squirrel, Commented Apr 23, 2019 at 8:11

Squirrel · Accepted Answer · 2019-04-23 11:00:38Z

Without knowing your table schema, I am just guessing ...

I have changed the JOIN condition to INCIDENT_REPORT.INCIDENT_REPORT_ID = INCIDENT_MAIN.INCIDENT_REPORT_ID. If the column name is wrong, please amend accordingly

INSERT INTO PROJECT_PROFILE 
            (
                -- INCIDENT_REPORT_ID,  remove this line
                PROJECT_NAME,
                PROJECT_TYPE, 
                PROJECT_PHASE, 
                PROJECT_START_DATE,
                PROJECT_END_DATE,
                DOMESTIC_INTERNATIONAL,
                PROJECT_FACILITIES_LOGISTIC
            )
SELECT     PROJECT_NAME, 
           PROJECT_TYPE, 
           PROJECT_PHASE,  
           PROJECT_START_DATE, 
           ESTIMATED_PROJECT_END_DATE, 
           DOMESTIC_INTERNATIONAL, 
           PROJECT_FACILITIES_LOGISTIC
FROM       INCIDENT_MAIN INCIDENT_REPORT_ID
INNER JOIN INCIDENT_REPORT 
        ON INCIDENT_REPORT.INCIDENT_REPORT_ID = INCIDENT_MAIN.INCIDENT_REPORT_ID

My advice is, don't insert into the table. First you execute the query, staring from the SEELCT to the end. Make sure the result is what you wanted. Then you add the INSERT statement.

Collectives™ on Stack Overflow

How to add data into the column with null values from another table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related