指定了4列的更新表，但是只有2列可用 [英] update table with 4 columns specified, but only 2 columns are available

问题描述

我有一个名为test的表，该表有4列：

I have one table called test, which has 4 columns:

id     INT
v_out  INT
v_in   INT
label  CHARACTER

我正在尝试使用以下查询更新表：

I'm trying to update the table with the following query:

String sql = "
    update
        test
    set
        v_out = temp.outV
        , v_in = temp.inV
        , label = temp.label
    from (
            values(
                (1,234,235,'abc')
                ,(2,234,5585,'def')
            )
        ) as temp (e_id, outV, inV, label)
    where
        id = temp.e_id
";

当我执行它时，出现错误：

When I execute it, I got the error:

org.postgresql.util.PSQLException：错误：

org.postgresql.util.PSQLException: ERROR:

 table "temp" has 2 columns available but 4 columns specified

有什么问题，我该如何解决？

Whats the problem, and how can i solve it?

推荐答案

不得将 values 子句的值放在括号中：

The values for the values clause must not be enclosed in parentheses:

values (
    (1,234,235,'abc'), (2,234,5585,'def')
) 

创建具有两列的单行。每列都是具有4个字段的匿名记录。

creates a single row with two columns. Each column being an anonymous "record" with 4 fields.

您想要的是：

from (
        values 
            (1,234,235,'abc'), 
            (2,234,5585,'def')
    ) as temp (e_id, outV, inV, label)

SQLFiddle显示差异： http://sqlfiddle.com/#!15/d41d8/2763

SQLFiddle showing the difference: http://sqlfiddle.com/#!15/d41d8/2763

此行为已记录在案，但这很难找到：

http://www.postgresql.org/ docs / current / static / rowtypes.html＃AEN7362

This behavior is documented, but that is quite hard to find:
http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7362

与some_table中的 select（col1，col2）基本相同与相比，从some_table 选择col1，col2。第一个返回具有匿名复合类型的列，该复合类型具有两个字段。第二个返回表中的两列。

It's essentially the same thing as select (col1, col2) from some_table vs. select col1, col2 from some_table. The first one returns one column with an anonymous composite type that has two fields. The second one returns two columns from the table.

这篇关于指定了4列的更新表，但是只有2列可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！