这是查询:
UPDATE einvoice_message AS e1
SET message = null
FROM (
SELECT *
FROM einvoice_message
where created < :created
ORDER BY :created LIMIT :limit
FOR UPDATE SKIP LOCKED
) AS e2
WHERE e1.einvoice_message_id = e2.einvoice_message_id
RETURNING e1.einvoice_message_id
我得了SqlExceptionHelper - The column name created was not found in this ResultSet.
不明白为什么在哪里?需要帮助。
Java代码:
@Query(value = "UPDATE einvoice_message AS e1 SET message = null FROM (SELECT * FROM einvoice_message
where created < :created ORDER BY :created LIMIT :limit FOR UPDATE SKIP LOCKED) AS e2 WHERE
e1.einvoice_message_id = e2.einvoice_message_id RETURNING e1.einvoice_message_id", nativeQuery = true)
List<EinvoiceMessage> deleteEinvoiceMessageContent(@Param("created") OffsetDateTime created,
@Param("limit") int limit);
这是我的桌子ddl:
create table einvoice_message
(
einvoice_message_id bigserial not null
constraint einvoice_message_pk
primary key,
message bytea not null,
created timestamp default now() not null
);
将:created
变量更改为列created
并不能解决问题。
问题出在this的返回值上。RETURNING e1.einvoice_message_id
将返回一个数字,而不是我提到的列表。正确的答案是:
@Query(value = "UPDATE einvoice_message AS e1 SET message = null FROM (SELECT * FROM einvoice_message
where created < :created ORDER BY :created LIMIT :limit FOR UPDATE SKIP LOCKED) AS e2 WHERE
e1.einvoice_message_id = e2.einvoice_message_id RETURNING e1.einvoice_message_id", nativeQuery = true)
int deleteEinvoiceMessageContent(@Param("created") OffsetDateTime created,
@Param("limit") int limit);