时间戳

首页 > 解决方案 > 如何在postgresql的另一个表中提取非唯一列

sql - 如何在postgresql的另一个表中提取非唯一列

问题描述

我有sample_source一个包含 4 列的表：

---------------------------------------------------
|id  | name | target_value | another_target_value |
===================================================
|1   | John |  'wow'       |   'value'            |
---------------------------------------------------
|2   | Liza |  'wow'       |   'value'            |
---------------------------------------------------
|3   | Mark |  'awesome'   |   'value'            |
---------------------------------------------------
|4   |Daniel|  'awesome'   |   'value'            |
---------------------------------------------------

我想提取target_value 和another_target_value表values（复制）

尝试写类似的东西

with cte as (insert into values(target_value, another_target_value)
             select target_value, another_target_value from sample_source t1
             returning id, t1.id as source_id)
update 
    sample_source
set
    value_id = cte.id
where
    sample_source.id = cte.source_id

基于这个讨论 ，但我不能sample_source在返回子句中使用表

UPD预期结果：

sample_source
------------------------
|id  | name | value_id |
========================
|1   | John |  1       |
------------------------
|2   | Liza |  2       |
------------------------
|3   | Mark |  3       | 
------------------------
|4   |Daniel| 4        |
------------------------

values
--------------------------------------------
|id  | target_value | another_target_value |
============================================
|1   |  'wow'       |   'value'            |
--------------------------------------------
|2   |  'wow'       |   'value'            |
--------------------------------------------
|3   |  'awesome'   |   'value'            |
--------------------------------------------
|4   |  'awesome'   |   'value'            |
--------------------------------------------

当然我有create，，alter和其他脚本，只有移动数据的问题

标签： sqlpostgresql

解决方案

没有重复id是很奇怪的。您可以随后在每个表中枚举以执行您想要的操作：

with i as (
      insert into vals (target_value, another_target_value)
          select target_value, another_target_value
          from sample_source t1
          returning id, t1.id as source_id
     ),
     i2 as (
      select i.*,
             row_number() over (partition by target_value, another_target_value order by target_value) as seqnum
      from i
     )
update sample_source ss
    set value_id = i2.id
from (select ss2.*,
             row_number() over (partition by target_value, another_target_value order by target_value) as seqnum
      from sample_source ss2
     ) ss2 join
     i2
     on i2.target_value = ss2.target_value and
        i2.another_target_value = ss2.another_target_value and
        i2.seqnum = ss2.seqnum
where ss.id = ss2.id;

推荐阅读