问题:
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