0

It is my first question in this forum. I wrote this code, but it is incorrect and I can't find a problem. I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom). If table sciana is empty I want to sciana.id = sciana.gid, but if sciana is not null I want to find max(sciana.id) + sciana1.gid

UPDATE sciana1
SET id =
CASE
WHEN sciana.gid IS NULL THEN
    id = sciana1.gid
WHEN sciana.gid IS NOT NULL THEN
    (SELECT max(sciana.id)+sciana1.gid 
    FROM sciana )       
END;

First example:

`sciana` IS EMPTY, so after the update `sciana1` looks like

         1,1,geom1
         2,2,geom2
         ...

Second example:

sciana IS NOT EMPTY

         1,1,geom1,
         ...
         9,9,geom9

so after the update, sciana1 contains

         1,10,geom10
         2,11,geom11
         ...

closed as unclear what you're asking by Andriy M, Philᵀᴹ, James Anderson, Mike Walsh, Marian Nov 24 '16 at 17:49

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • It's not very clear what you want to do. I suggest you add a few sample rows from both tables and what you want as a result (after the UPDATE). And also add the CREATE TABLE statements of both tables so we know datatypes and UNIQUE / PRIMARY KEY constraints. – ypercubeᵀᴹ Nov 24 '16 at 0:24
  • Does the sciana table have only 1 (or 0) rows? – ypercubeᵀᴹ Nov 24 '16 at 0:49
  • are you trying to set this field as IDENTITY column? – McNets Nov 24 '16 at 9:56
  • So, a table is never NULL (or NOT NULL). It can be empty (or not empty). NULL applies to values. – dezso Nov 24 '16 at 10:03
2

I think you want to do something like

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0);

COALESCE() will return you the first non-NULL parameter. Then max(id) is NULL for an empty table, which means the COALESCE expression returns 0 when the table is empty, and the actual maximum when it is not.

You can further improve this by checking if the value will be different after the update, thus sparing unnecessarily bloating the table:

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0)
 WHERE id IS DISTINCT FROM gid + COALESCE((SELECT max(id) FROM sciana), 0);

IS DISTINCT FROM is an operator that behaves similarly to <>, but also considers NULLs. With a plain <> you might not get anything updated, as the comparison itself results in NULL (thus not matching any rows) if sciana1.id is empty.

One more important thing is that if sciana is big, an index on id can help a lot in finding its maximum.

And a last note: in the second UPDATE query, the SELECT max()... part is executed twice. This can be prevented by moving it out to a CTE or a subquery like

UPDATE sciana1
   SET id = gid + COALESCE(m, 0)
  FROM (SELECT max(id) FROM sciana) AS t(m)
 WHERE id IS DISTINCT FROM gid + COALESCE(m, 0);

One should check which one results in a more efficient execution plan. In my experiments, the latter produces a nested join, which with a higher number of rows in both tables gets slower than the first version, while being faster with low row numbers.

  • Thanks dezso and other. Coalesce it is what I'm looking for. – Hubert Bujno Nov 24 '16 at 11:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.