I am trying to solve this problem. Here is my question that I am trying to solve using SQL.

1. I have a project table which has many columns with data; such as (ID, TITLE, DESCRIPTIO,..ETC).

2. I have another table called field table; the field table has thousands of questions(it is similar to a survey poll with questions).

3. Each project has specific questions from the field table.

4. One of the questions in the field table is called record_id, some projects does not have this record_id.

5. Therefore, I sorted all the projects that does not have record_id using a SQL subquery; I was successfully able to sort the projects without record_id.

6. I am confused and stuck on how to use insert statement to insert record_id to those projects without record_id.

SELECT 
    PROJECT.PROJECTID
FROM
    PROJECT 
WHERE   
    PROJECTID NOT IN (SELECT PROJECT.PROJECTID
                      FROM PROJECT
                      JOIN FIELD ON PROJECT.PROJECTID = FIELD.PROJECTID
                      WHERE FIELD.ISPROJECTID = 1);

I have tried to use this query, but it is not working.

INSERT INTO FIELD (NAME, LABEL, DATATYPE, ALIGNMENT, ISPROJECTID)
VALUES ('record_id', 'Record ID', 'Text', 'RV', 1);

SELECT 
    PROJECT.PROJECTID
FROM 
    PROJECT 
WHERE 
    PROJECTID NOT IN (SELECT PROJECT.PROJECTID
                      FROM PROJECT
                      JOIN FIELD ON PROJECT.PROJECTID = FIELD.PROJECTID
                      WHERE FIELD.ISPROJECTID = 1);

CodePudding user response：

Most databases have an INSERT INTO SELECT.

you should check with the documentation of your database, if it differs

INSERT INTO FIELD (NAME, LABEL, DATATYPE,ALIGNMENT,
                   ISPROJECTID)  
SELECT 'record_id','Record ID','Text','RV', PROJECT.PROJECTID
                FROM PROJECT WHERE PROJECTID NOT IN
                 (
                 SELECT PROJECT.PROJECTID
                 FROM PROJECT
                JOIN FIELD
        ON PROJECT.PROJECTID = FIELD.PROJECTID
                WHERE FIELD.ISPROJECTID = 1
                   );

CodePudding user response：

what you need to insert, this VALUES( 'record_id','Record ID','Text','RV',1); or you are select values from another tables!

if you insert from another table you should use like this VALUES => select record1,record2,record3,record4,record5 from table