SQL Insert Into ColX Table 1 Select ColY from table 2 where Table1.colA=Table2.ColB

Question

I'm much more a java than a sql developer. I have looked on SO for this, but for some reason cannot find a question with similar WHERE criteria

In PostgreSQL, the following syntax is incorrect

INSERT INTO contentelementtypeprogressortype  (contentelementtypeclassname)
SELECT contentelementtypename FROM contentelementtype
WHERE
contentelementtype.id=contentelementtypeprogressortype.contentelementtypeid

The error is:

ERROR: invalid reference to FROM-clause entry for table "contentelementtypeprogressortype" LINE 2: ...OM contentelementtype where contentelementtype.id=contentele... ^ HINT: There is an entry for table "contentelementtypeprogressortype", but it cannot be referenced from this part of the query. ********** Error **********

ERROR: invalid reference to FROM-clause entry for table "contentelementtypeprogressortype" SQL state: 42P01 Hint: There is an entry for table "contentelementtypeprogressortype", but it cannot be referenced from this part of the query. Character: 159

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-01-28T22:14:54

This is your query:

INSERT INTO contentelementtypeprogressortype(contentelementtypeclassname)
    SELECT cet.contentelementtypename
    FROM contentelementtype  cet
    WHERE cet.id = contentelementtypeprogressortype.contentelementtypeid;

I don't think you really want an INSERT -- this adds a new row. I think you really want an UPDATE, which changes a value in an existing row:

UPDATE contentelementtypeprogressortype cetpt
    SET contentelementtypeclassname = cet.contentelementtypename
    FROM contentelementtype  cet
    WHERE cet.id = cetpt.contentelementtypeid;

SQL Insert Into ColX Table 1 Select ColY from table 2 where Table1.colA=Table2.ColB

1 Answers