1

I am having 2 tables in PostgreSQL, have to fetch total records from both table . If count > 50 then update status=5 of oldest record. Here is query for oldest record. Have to update oldest record in single query. Here is query I have tried to find oldest record

 (select   'SPORTS' AS eventType,activity.articleId,activity.title,activity.articleText,
           activity.createdDate,activity.creatorscreenname as creatorScreenNameStr
  FROM     SPORTS activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   'MUSIC' AS musicType,activity.wikiTopicId as articleId,
           activity.title as title,activity.problemDescription as articleText,
           activity.createdDate,activity.creatorScreenName as creatorScreenNameStr
  FROM     MUSIC activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 ORDER BY 5 ASC limit 1
3
  • 1
    limit 1will never return 50 records
    – McNets
    May 2, 2017 at 12:19
  • i am looking for only oldest record. May 2, 2017 at 12:23
  • Consider breaking down your problem into several queries and combine them in a plpgsql DO block. May 2, 2017 at 13:26

1 Answer 1

1

I've set up next example:

create table sports(id int, status int, createdBy int, createdDate date);
create table music(id int, status int, createdBy int, createdDate date);
insert into sports values (1, 1, 101, '20170101'), (2, 2, 101, '20170102'), (3, 1, 102, '20170103');
insert into music  values (1, 1, 101, '20170201'), (2, 2, 101, '20170202'), (3, 1, 102, '20170203');
with sel as
(
    select ttype, id, status, createdBy, createdDate
    from 
        (
        (select 'sports' as ttype, id, status, createdBy, createdDate
        from   sports
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        union
        (select 'music' as ttype, id, status, createdBy, createdDate
        from   music
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        ) un
    order by 5
    limit 1
)
    , updsport as
    (
        update sports
        set    status = 5
        from   sel
        where  sports.id = sel.id
        and    sel.ttype = 'sports'
    )
    update music
    set    status = 5
    from   sel
    where  music.id = sel.id
    and    sel.ttype = 'music'
    ;
select * from sports order by id;
id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      5 |       101 | 2017-01-01 
 2 |      2 |       101 | 2017-01-02 
 3 |      1 |       102 | 2017-01-03 
select * from music order by id;
id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      1 |       101 | 2017-02-01 
 2 |      2 |       101 | 2017-02-02 
 3 |      1 |       102 | 2017-02-03 

Now try by changing createdDate on sports or music table just to change the filter criteria.

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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