SQL插入ColX表1从表2中选择ColY，其中Table1.colA = Table2.ColB [英] SQL Insert Into ColX Table 1 Select ColY from table 2 where Table1.colA=Table2.ColB

问题描述

我比Java开发人员更多的是Java。为此，我已经看过了，但是出于某种原因找不到与WHERE条件相似的问题

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

在PostgreSQL中，以下语法不正确

In PostgreSQL, the following syntax is incorrect

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

错误是：

错误：对表 contentelementtypeprogressortype
的FROM子句条目的引用无效第2行：... OM contentelementtype其中contentelementtype.id = contentele ...
^
提示：表 contentelementtypeprogressortype的条目，但不能从查询的此部分引用。
**********错误**********

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 **********

错误：对FROM子句条目的引用无效表 contentelementtypeprogressortype
SQL状态：42P01
提示：表 contentelementtypeprogressortype有一个条目，但是不能在查询的这一部分中引用它。
字符：159

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

推荐答案

这是您的查询：

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

我不认为您真的想要 INSERT -这将添加新行。我认为您确实想要 UPDATE ，它可以更改现有行中的值：

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插入ColX表1从表2中选择ColY，其中Table1.colA = Table2.ColB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！