Postgresql – Update table where table name in select query itself

postgresqlpostgresql-9.3

I am having following select query 

 (select   article.articleId ,'MUSIC' as type ,createddate 
  FROM     MUSICarticle 
  where    article.status=2  
  and      article.createdBy=101 
  order by createdDate ASC limit 1) 
 UNION 
 (select   article.articleId, 'SPORTS' as type ,createddate  
  FROM     SPORTSarticle 
  where    article.status=2  
  and      article.createdBy=101 
  order by createdDate ASC limit 1)
 UNION  
 (select   article.articleId ,'DANCE' as type  ,createddate 
  FROM     DANCE 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     SINGING article 
  where    article.status=2  
  and      article.createdBy=101 
  order by createdDate ASC limit 1) 
 order by 3 ASC limit 1

and having output 

articleid |  type   |        createddate
----------+---------|-------------------------
    1     |  MUSIC  | 2017-04-28 12:53:55.649

Now have to update MUSIC table where id =1 .

Best Answer

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
$$;
Related Question