CODEWITHSUNDEEP
sqlpostgresql
IT_info
IT_info

Reputation: 727

Updates which include auto increment column

I am using PostgreSQL and I would like to update a table which would include an auto number column id. I know it might something very simple but I have done something like this;

CREATE SEQUENCE  SEQ_ID
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

update trk2
set (id, track_id, track_point) =
           (select nextval('seq_id'), trk1.track_fid, trk1.wkb_geometry
              from track_points_1 as trk1
             where track_fid = 0)

The thing is that it is giving me the following error (code reformatted):

ERROR:  syntax error at or near "select"
LINE 3: set (id, track_id, track_point)=(select nextval('seq_id'), t...

Can anybody help please?

Upvotes: 0

Views: 495

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657922

Your query is broken in several places. It could look something like this:

UPDATE trk2
SET   (id, track_id, track_point) = 
      (next_id, x.track_fid, x.wkb_geometry)
FROM  (
   SELECT id
         ,nextval('seq_id') AS next_id
         ,track_fid
         ,wkb_geometry
   FROM   track_points_1
   WHERE  track_fid = 0
   ) x
WHERE  trk2.id = x.id;  -- adapt to your case

Or simpler (preferable syntax):

UPDATE trk2
SET   (id, track_id, track_point) = 
      (nextval('seq_id'), x.track_fid, x.wkb_geometry)
FROM   track_points_1 x
WHERE  x.track_fid = 0
AND    trk2.id = x.id;   -- adapt to your case

Major points:

  • An UPDATE without a WHERE clause only makes sense if you really need to change each and every row in the table. Else it is wrong or at least sub-optimal.

    When you retrieve values from another table, you get a CROSS JOIN between target and source if you don't add a WHERE clause connecting target with source - meaning that every row of the target table will be updated with every row in the source table. This can take a very long time and lead to arbitrary results. The last UPDATE wins. In short: this is almost always complete nonsense and extremely expensive at that.

    In my example I link target and source by the id column. You have to replace that with whatever fits in your case.

  • You can assign multiple values in one SET clause in an UPDATE, but you can only return a single value from a correlated subselect expression. Therefore, it is strictly not possible to have a subselect in a SET clause with multiple values.

  • Your initial syntax error comes from a missing pair of parenthesis around your subselect. But adding that only reveals the error mentioned above.

  • Depending on what you are after, you would include nextval('seq_id') in the subquery or in the SET clause directly. This can lead to very different results, especially when you have rows in the subquery that are not used in the UPDATE.

    I placed it in the SET clause because I suspect, that's what you want. The sequence of rows is still arbitrary. If you want more control over which numbers are assigned, you need to define what you want and then take a different route.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 754520

I think you need a second set of parentheses on the RHS of the SET clause:

UPDATE trk2
SET (id, track_id, track_point) =
          ((SELECT nextval('seq_id'), trk1.track_fid, trk1.wkb_geometry
              FROM track_points_1 as trk1
             WHERE track_fid = 0));

The first set of parentheses matches the parentheses on the LHS of the SET clause. The second set wraps up the sub-query for re-use.

Upvotes: 0

losthorse
losthorse

Reputation: 1570

Tyler Eaves is correct, this action is not advisable.

However, if you insist, this may help:

Once you have a sequence on a column you don't have to call nextval() in the SET statement. Just leave it out and the column will auto-increment.

UPDATE
    trk2
SET 
    (
        track_id = [track_id Value],
        track_point = [track_point Value]
    )

or, include it and set it to default or null

UPDATE
    trk2
SET 
    (
        id = default,
        track_id = [track_id Value],
        track_point = [track_point Value]
    )

Using your example:

UPDATE
    trk2
SET 
    (
        track_id, 
        track_point
    ) = (
        SELECT 
            trk1.track_fid,
            trk1.wkb_geometry
        FROM 
            track_points_1 AS trk1
        WHERE
            track_fid = 0
    )

Upvotes: 1

Related Questions