如何用更少SQL查询删除有序列表行并更新位置（基于sqlc）

问题背景 #

我需要编写SQL查询，并使用sqlc生成对应的Go代码来执行这些查询。我的items表结构及数据如下：

我正在编写一个删除API，删除指定项后需更新剩余项的位置以消除间隙。例如，删除ball后，表格应变为：

即删除目标项后，将所有位置大于目标项位置的项的位置减1。

当前实现方式 #

我当前的SQL语句如下：

-- GetItem: one
SELECT * from items WHERE id=?;

-- name: DeleteItem:execrows
DELETE FROM items WHERE id = ?;

-- name: UpdatePositions: execrows
UPDATE items SET position = position - 1
WHERE position > sqlc.arg(position);

对应的Go代码调用逻辑：

item, _ := GetItem(id)
_, _ := DeleteItem(id)
_, _ := UpdatePositions(item.position)

当前流程为先获取待删除项的位置，再执行删除，最后更新位置，我希望减少该删除操作的函数调用次数。

疑问 #

1. 是否可以通过sqlc生成单个函数，同时执行DELETE和UPDATE两条SQL语句？
2. 是否可以省略GetItem调用，用单条SQL语句完成位置获取与更新？

我曾尝试编写包含目标项位置查询与更新的SQL语句，但出现了sqlc错误。

解决方案 #

问题1：用sqlc生成单函数执行DELETE+UPDATE #

可以通过编写包含多条SQL语句的事务块，让sqlc生成单个函数来执行所有操作，同时保证数据一致性。

示例SQL：

-- name: DeleteItemAndUpdatePositions:exec
BEGIN;
-- 通过CTE获取待删除项的位置，用于后续更新
WITH deleted_item AS (
    SELECT position FROM items WHERE id = sqlc.arg(id)
)
UPDATE items 
SET position = position - 1
WHERE position > (SELECT position FROM deleted_item);

-- 执行删除操作
DELETE FROM items WHERE id = sqlc.arg(id);
COMMIT;

sqlc会基于这段SQL生成一个名为DeleteItemAndUpdatePositions的函数，调用时只需传入目标ID即可，无需拆分多次调用。

问题2：省略GetItem调用，单SQL完成所有操作 #

上述SQL已经实现了这一点——通过CTE（公共表表达式）deleted_item直接获取待删除项的位置，紧接着执行位置更新，最后删除目标项，全程不需要单独调用GetItem。

注意事项：

• 必须用事务包裹两个操作，确保要么全部成功，要么全部回滚，避免出现删除成功但位置更新失败的不一致情况。
• 如果传入的ID不存在，CTE返回空结果，UPDATE语句不会修改任何数据，DELETE也不会有影响，逻辑安全。

生成的Go代码调用示例 #

_, err := db.DeleteItemAndUpdatePositions(ctx, 107)
if err != nil {
    // 处理错误逻辑
}

内容的提问来源于stack exchange，提问作者sonu