Update table where table name in select query itself
You could use a stored procedure or anonymous PL block to build dynamic SQL. See below for an example.


  DISCLAIMER: I must point out that dynamic SQL is almost never the way to go - the database cannot cache the query plans efficiently and it is much slower than conventional queries. Were we on Stack Overflow here I'd be more inclined to suggest you use a supplementary language with a switch statement or something to accomplish this.


do language plpgsql $$
declare
    _sql text;
    _r record;
begin
    for _r in (
        select   article.articleId ,'MUSIC' as type ,createddate 
        FROM     MTDDiscussion article 
        where    article.status=2  
        and      article.createdBy=101 
        order by createdDate ASC limit 1) 
        UNION 
        (select   article.articleId, 'SPORTS' as type ,createddate  
        FROM     MTDOpinion article 
        where    article.status=2  
        and      article.createdBy=101 
        order by createdDate ASC limit 1)
        UNION  
        (select   article.articleId ,'DANCE' as type  ,createddate 
        FROM     MTDSurvey article 
        where    article.status=2  
        and      article.createdBy=101 
        order by createdDate ASC limit 1) 
        UNION  
        (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate  
        FROM     WikiTopic article 
        where    article.status=2  
        and      article.createdBy=101 
        order by createdDate ASC limit 1
    ) loop
        _sql = E'update ' || _r.type::text || E' set COLUMNNAME = \'' || _r.createddate::text || E'\'::date where id = ' || _r."articleId"::text;
        execute _sql;
    end loop;
end
$$;