Update table column with value from inner query?

Question

I have the following table:

create table public.dctable
(
     prod int, 
     customer varchar(100), 
     city varchar(100), 
     num int, 
     tim datetime, 
     dc smallint
);

insert into dctable 
values (1, 'Jim', 'Venice', 5, '2015-08-27 1:10:00', 0),
       (1, 'Jim', 'Venice', 5, '2015-08-27 1:10:15', 0),
       (1, 'Jim', 'Venice', 5, '2015-08-27 1:10:28', 0),
       (4, 'Jane', 'Vienna', 8, '2018-06-04 2:20:43', 0),
       (4, 'Jane', 'Vienna', 8, '2018-06-04 2:20:45', 0),
       (4, 'Jane', 'Vienna', 8, '2018-06-04 2:20:49', 0),
       (4, 'Jane', 'Vienna', 8, '2018-06-04 2:30:55', 0),
       (7, 'Jack', 'Vilnius', 4, '2015-09-15 2:20:55', 0),
       (7, 'Jake', 'Vigo', 9, '2018-01-01 10:20:05', 0),
       (7, 'Jake', 'Vigo', 2, '2018-01-01 10:20:25', 0);

Now I want to update the column dc to the value of tdc in this query:

select 
    t.*,
    (case 
        when lead(tim) over (partition by prod, customer, city, num order by tim) <= dateadd(second, 30, tim)
           then 1
           else 0
     end) as tdc
from 
    public.dctable t

So I have tried this:

update public.dctable
set dc = b.tdc
from 
    (select 
         t.*,
         (case 
             when lead(tim) over (partition by prod, customer, city, num order by tim) <= dateadd(second, 30, tim)
                then 1
                else 0
          end) as tdc
     from    
         public.dctable t) b
where 
    public.dctable.prod = b.prod
    and public.dctable.customer = b.customer
    and public.dctable.city = b.city
    and public.dctable.num = b.num;

But when I query the results, dc is still 0 for all rows.

select * from public.dctable;

prod        customer    city    num tim                   dc
-------------------------------------------------------------
1           Jim         Venice  5   2015-08-27 01:10:00   0
1           Jim         Venice  5   2015-08-27 01:10:28   0
1           Jim         Venice  5   2015-08-27 01:10:15   0
4           Jane        Vienna  8   2018-06-04 02:20:49   0
4           Jane        Vienna  8   2018-06-04 02:20:45   0
4           Jane        Vienna  8   2018-06-04 02:30:55   0
4           Jane        Vienna  8   2018-06-04 02:20:43   0
7           Jake        Vigo    2   2018-01-01 10:20:25   0
7           Jack        Vilnius 4   2015-09-15 02:20:55   0
7           Jake        Vigo    9   2018-01-01 10:20:05   0

How can I get it to update the column dc to the value of tdc from the inner query above?

Thanks

your varchar needs a length or it will get the default (8) and your dta would be truncated. You also can't insert a datetime into a timestamp... change that to datetime. Just a heads up — S3S, Jun 29 '18 at 16:32

score 0 · Accepted Answer · answered Jun 29 '18 at 16:41

This seems to be what you want.

SQL Fiddle

update d
  set d.dc = b.dc2
from dctable d
inner join 
      (select 
         *, 
         dc2 = case 
           when lead(tim) over (partition by prod, customer, city, num order by tim) <= dateadd(second, 30, tim)
           then 1
           else 0
         end
       from dctable) b on
            d.prod = b.prod
        and d.customer = b.customer
        and d.city = b.city
        --and d.tim = b.tim    --you may also want this join clause.
        and d.num = b.num;



select * from dctable

yes, i had forgotten the time in the join, that did it. Thanks. — Kenobi, Jun 29 '18 at 18:03

Update table column with value from inner query?

1 Answers1