时间戳

首页 > 解决方案 > 将数据移动到正确的记录

sql - 将数据移动到正确的记录

问题描述

我有一张需要更正数据的表格。以下是一条记录的示例。基本上，销售封闭单元中的数据需要在 Agent_to_Agent Ref close_unit 中。我已经尝试了我能想到的每一种不同的方法，但我无法弄清楚。我敢肯定这很简单，我想我只是在错误地寻找方法。任何帮助是极大的赞赏！

当前（不良）数据：

+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+
| sale_no | payeeID |      ComType       | close_units |   record_type   | ref_agent_type | referring_agentID | ref_side |
+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+
|    7586 |    1001 | Listing            |           1 | Listing         | NULL           |                 0 |          |
|    7586 |    2001 | Selling            |           1 | Selling         | NULL           |                 0 |          |
|    7586 |    3254 | NULL               |           0 | Off The Top Ref | NULL           |                 0 | L        |
|    7586 |    4684 | Agent to Agent Ref |           0 | Agent Paid Ref  | Selling        |              2001 |          |
+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+

预期结果：

+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+
| sale_no | payeeID |      ComType       | close_units |   record_type   | ref_agent_type | referring_agentID | ref_side |
+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+
|    7586 |    1001 | Listing            |           1 | Listing         | NULL           |                 0 |          |
|    7586 |    2001 | Selling            |           0 | Selling         | NULL           |                 0 |          |
|    7586 |    3254 | NULL               |           0 | Off The Top Ref | NULL           |                 0 | L        |
|    7586 |    4684 | Agent to Agent Ref |           1 | Agent Paid Ref  | Selling        |              2001 |          |
+---------+---------+--------------------+-------------+-----------------+----------------+-------------------+----------+

标签： sqltsql

解决方案

以下查询会将值复制到“代理到代理参考”行：

update my_table t1 set close_units = (
  select close_units from my_table t2 
    where t2.sale_no = t1.sale_no and t2.ComType = 'Selling'
  )
  where ComType = 'Agent to Agent Ref';

这会将“销售”值重置为零：

update my_table t1 
  set close_units = 0
  where ComType = 'Selling'
    and exists (
      select close_units from my_table t2 
        where t2.sale_no = t1.sale_no and t2.ComType = 'Agent to Agent Ref'
    )

推荐阅读