1

I am trying to update my date field.
Table structure is like:

date           id 
2016-11-14     1
2016-11-14     2
2016-11-14     3
2016-11-14     4
-
-
-
2016-11-14     100

How to update first ten records with different date, second ten records with different date and so on?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
vithika
  • 67
  • 1
  • 7

2 Answers2

1
UPDATE tbl t
SET   "date" = date '2016-11-14' + sub.rn::int / 10  -- integer division
FROM  (
   SELECT id, row_number() OVER (ORDER BY id) AS rn
   FROM   tbl
   ) sub
WHERE t.id =  sub.id;

The subquery computes a gapless row number, since nothing in your question says the id is actually guaranteed to be without gaps.

You can just add an integer to an actual date to add a days. (Forgot the cast to int in my first version.)

For timestamp use instead:

timestamp '2016-11-14' + interval '1 day' * (sub.rn / 10)
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Sorry i am not able to achieve it.I am getting error here "ERROR: operator does not exist: date + bigint LINE 2: SET date = date '2016-11-14' + (sub.rn / 10) " – vithika Nov 26 '16 at 05:56
  • need something like this `'2016-11-30'::timestamp + INTERVAL sub.rn || ' day' END;` – Juan Carlos Oropeza Nov 26 '16 at 05:59
  • @JuanCarlosOropeza: Wouldn't work with `timestamp` - where I would use the expression `timestamp '2016-11-14 0:0' + interval '1 day' * (sub.rn / 10)` - but that's not the question. – Erwin Brandstetter Nov 26 '16 at 06:02
  • @ErwinBrandstetter still have some error. http://rextester.com/LAAQ86247 – Juan Carlos Oropeza Nov 26 '16 at 06:12
  • @JuanCarlosOropeza: No, I don't. Your test incorrectly uses `timestamp` instead of `date`. I already told you that's wrong. This is the right test: http://rextester.com/LAAQ86247 – Erwin Brandstetter Nov 26 '16 at 13:36
  • @ErwinBrandstetter you send the same link. In rextester you need to press `save` to generate the new link. Now we know OP was talking about `timestamp` maybe irrelvant, but still will like to know how looks like, because dont use `date` very often. – Juan Carlos Oropeza Nov 26 '16 at 14:14
  • nevermind I did it myself. Q: is there a difference between `date '2016-11-14' ` and `'2016-11-14' ::date`, I knew the second one, the result look the same, dont know why have two sintaxis for the same. – Juan Carlos Oropeza Nov 26 '16 at 15:42
  • 1
    @JuanCarlosOropeza: Sorry, pasted the old link. Here is the new link: http://rextester.com/GKIPQ42219. A: There is a very minor difference. The 1st one casts the literal to `date` directly. The 2nd one defaults to type `text` and is *then* cast to `date`. So `date '2016-11-14'` is a tiny bit faster and 1 character shorter. You won't be able to measure the difference. Details: http://stackoverflow.com/a/13676871/939860 – Erwin Brandstetter Nov 27 '16 at 01:33
0

You could use a CASE

  UPDATE yourTable
  SET "date" = CASE WHEN id <= 10 then '2016-11-01'::timestamp 
                    WHEN id <= 20 then '2016-11-02'::timestamp 
                    ....
                    WHEN id <= 100 then '2016-11-10'::timestamp 
               END;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118