Update with results of another sql

Question

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

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
    )

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

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

`where code like '%1'` will select codes that *end* with `1` it will **not** select the code 100. To get that, you'll have to use `code like '1%'` — , Aug 17 '14 at 16:17

score 0 · Accepted Answer · answered Aug 17 '14 at 16:12

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 example: http://sqlfiddle.com/#!15/745a7/1

Erwin Brandstetter · Answer 2 · 2014-08-21T16:55:22.630

UPDATE tbl_a a
SET    sequence = b.next_seq
FROM  (
   SELECT code, max(sequence) + 1 AS next_seq
   FROM   tbl_b
   GROUP  BY code
   ) b
WHERE a.code = b.code;

SQL Fiddle.

Only columns of the target table can be updated. It would not make sense to table-qualify those. Consequently, this is not allowed.
Every subquery must have a table alias for the derived table.
I would not use a CTE for a simple UPDATE like this. A subquery in the FROM clause is typically simpler and faster.
No point in double-quoting the aggregate function count(). No pint in double-quoting perfectly legal, lower case identifiers, either. No point in table-qualifying columns in a subquery on a single table in a plain SQL command (no harm either).
You don't need a WHERE condition, since you want to UPDATE all rows (as it seems). Note that only row with matching code are updated. Other rows in tbl_b remain untouched.

Basically you need to read the manual about UPDATE before you try any of this.

Update with results of another sql

2 Answers2

Linked