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