为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:Ubuntu 18.04.5 LTS x86_64
【CPU】: Intel
【问题描述】*:使用merge into进行批量操作提示:SQL 错误 [22000]: 无法在源表中获得一组稳定的行
MERGE
INTO
INCEPTION_RBAC.user_used_project t1
USING (
SELECT
'ddc56cdb-e034-4857-bc93-edb579d27b07' AS user_id,
'395fa74a-e5f7-4b07-b72f-be8d613fd54e' AS tenant_id,
2000 AS project_id,
'2023-05-26 17:09:38.439' AS used_time
UNION ALL
SELECT
'ddc56cdb-e034-4857-bc93-edb579d27b07' AS user_id,
'395fa74a-e5f7-4b07-b72f-be8d613fd54e' AS tenant_id,
2000 AS project_id,
'2023-05-26 17:09:38.439' AS used_time
) t2 ON
(t1.user_id = t2.user_id
AND t1.project_id = t2.project_id)
WHEN MATCHED THEN
UPDATE
SET
t1.tenant_id = t2.tenant_id,
t1.used_time = t2.used_time
WHEN NOT MATCHED THEN
INSERT
(user_id,
tenant_id,
project_id,
used_time)
VALUES (t2.user_id,
t2.tenant_id,
t2.project_id,
t2.used_time);
错误:
SQL 错误 [22000]: 无法在源表中获得一组稳定的行
是因为using里面的子查询返回的匹配列(即 user_id )有重复数据导致。
使用了两条相同的批量数据导致的?我换了一条数据是可行的。