用另一个SQL的结果更新 [英] Update with results of another sql

问题描述

使用下面的sql，我计算每个代码在tableB中有多少条记录。将总计字段分配给计数结果，并将代码分配给记录的代码字段。

With the sql below I count how many records I have in tableB for each code. The total field is assigned the result of the count and the code the code field of the record.

SELECT
    "count" (*) as total,
    tableB."code" as code
FROM
    tableB 
WHERE
    tableB.code LIKE '%1'
GROUP BY
    tableB.code

在tableA中，我有一个sequence字段，并用total的结果（在上一个sql中获得）加上1的值进行更新。
我尝试了此操作，但没有用，有人可以帮我吗？

In tableA I have a sequence field and I update with the result of total (obtained in the previous sql) plus 1 Do this for each code. I tried this and it did not work, can someone help me?

UPDATE tableA
SET tableA.sequence = (tableB.total + 1) where tableA."code" = tableB.code
FROM
    (
        SELECT
            "count" (*) as total,
            tableB."code" as code
        FROM
            tableB 
        WHERE
            tableB.code LIKE '%1'
        GROUP BY
            tableB.code
    )

我为表编辑的内容就像是最相信facillita了解我的需求

I edited for my tables are as mostar believe facillita understanding of my need

tableA

code  sequence
100      null
200      null


table B

code  sequence
100      1
100      2
100      3
100      4
......
100      17
200      1
200      2
200      3
200      4
......
200      23

需要使用以下命令更新tableA中的序列空白字段数字18编码= 100

需要更新表A中的序列空白字段，数字24编码= 200

Need to update the sequence blank field in tableA with the number 18 to code = 100
Need to update the sequence blank field in tableA with the number 24 to code = 200

推荐答案

这假定代码在table_a中是唯一的：

This assumes that code is unique in table_a:

with max_seq as (
   select code,
          max(sequence) + 1 as max_seq
   from table_b
   group by code
) 
update table_a
  set sequence = ms.max_seq
from max_seq ms
where table_a.code = ms.code;

SQLFiddle示例： http://sqlfiddle.com/#!15/745a7/1

SQLFiddle example: http://sqlfiddle.com/#!15/745a7/1

这篇关于用另一个SQL的结果更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！