Inserting values from distinct on query into another table

问题: I have the following query which gives me back the columns cookie_hash, crdate, account_uuid and action. SELECT DISTINCT ON (cookie_hash) event.cookie_hash, event.crdate...

问题:

I have the following query which gives me back the columns cookie_hash, crdate, account_uuid and action.

SELECT DISTINCT ON (cookie_hash) event.cookie_hash, event.crdate, event.account_uuid, event.action
FROM event
WHERE cookie_hash IN (SELECT community_signup.cookie_hash FROM community_signup) AND 
event.action = 'signup'
ORDER BY cookie_hash, crdate DESC;

Then i have another table community_signup with the filled column cookie_hash and empty columns crdate and account_uuid.

How do i fill the the empty columns in table2 with the corresponding values to cookie_hash?


回答1:

Use a FROM clause in the UPDATE. The Postgres syntax looks like this:

UPDATE community_signup cs
    SET crdate = e.crdate,
        account_uuid = e.account_uuid
FROM (SELECT DISTINCT ON (e.cookie_hash) e.cookie_hash, e.crdate, e.account_uuid, e.action
      FROM event e
      WHERE e.cookie_hash IN (SELECT cs.cookie_hash
                              FROM community_signup cs
                             ) AND 
            e.action = 'signup'
      ORDER BY e.cookie_hash, e.crdate DESC         
     ) 
WHERE e.cookie_hash = cs.cookie_hash;

Note that I added table aliases so the query is easier to write and to read.

Also, the IN filter is not really necessary. The outer WHERE will handle that as well.


回答2:

I hope this is useful to you.

 UPDATE A
    SET A.crdate =B.crdate,
    A.account_uuid = B.account_uuid
    FROM community_signup AS A
    INNER JOIN 
    (
    SELECT Distinct cookie_hash, crdate, account_uuid, [action]
    FROM [event] WHERE cookie_hash IN (SELECT community_signup.cookie_hash FROM community_signup) AND 
    event.action = 'signup'
    ORDER BY cookie_hash, crdate DESC
    )AS B
    ON A.cookie_hash = B.cookie_hash
  • 发表于 2019-03-09 03:48
  • 阅读 ( 164 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除