数据仓库服务 GaussDB(DWS)-业务报错：unable to get a stable set of rows in the source table:解决方案

时间：2025-02-12 15:02:21

解决方案

方案一：设置参数behavior_compat_options为merge_update_multi。

当目标表匹配到多行满足条件时，该方案不会报错，而是会随机匹配一行数据，有数据遗漏风险。

     
          SET behavior_compat_options=merge_update_multi;MERGE INTO products p  USING newproducts np  ON (p.product_id = np.product_id)  WHEN MATCHED THEN  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE np.product_id = 1502;MERGE 1SELECT * FROM products ; product_id |  product_name  | category------------+----------------+-----------       1501 | vivitar 35mm   | electrncs       1502 | olympus camera | electrncs       1600 | play gym       | toys(3 rows)

方案二：修改MERGE INTO匹配条件。

尽可能选择筛选结果唯一的表达式为匹配条件。

    
         MERGE INTO products p  USING newproducts np  ON (p.product_id = np.product_id)  WHEN MATCHED THEN  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE np.product_id != 1502;MERGE 1SELECT * FROM products; product_id |  product_name  | category------------+----------------+-----------       1501 | vivitar 35mm   | electrncs       1502 | olympus camera | electrncs       1600 | lamaze         | toys(3 rows)