sql - 如果postgresql中的总记录数大于50，如何更新记录

Question

我在 PostgreSQL 中有 2 个表。我必须从两个表中获取总记录。如果 count >50，则更新 status=5 of oldest record 。这里是对 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

Answer 1

据我所知，您不能用一条语句更新一个表或另一个表。所以保持简单，写两个:

update sports
set status = 5
where (select count(*) from sports) + (select count(*) from music) > 50
and createddate = (select min(createddate) from sports)
and not exists (select * from music m where m.createddate < sports.createddate);

和

update music
set status = 5
where (select count(*) from music) + (select count(*) from sports) > 50
and createddate = (select min(createddate) from music)
and not exists (select * from sports s where s.createddate < music.createddate);

可能有一些方法可以更 Eloquent 地做到这一点，但我认为这很容易理解:更新日期是最小日期的记录，而另一个表中没有更早的日期。