Update records in Postgres from a select query 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
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