PostgreSQL中与MySQL查询的UPDATE等价语句

Question

PostgreSQL中与MySQL查询的UPDATE等价语句

3

我有一个简单的MySQL查询语句，现在想将其转换为PostgreSQL。但是我已经尝试了3天，还是不明白哪里出错了:

UPDATE webUsers u, 
(SELECT IFNULL(count(s.id),0) AS id, p.associatedUserId FROM pool_worker p 
LEFT JOIN shares s ON p.username=s.username 
WHERE s.our_result='Y' GROUP BY p.associatedUserId) a
SET shares_this_round = a.id WHERE u.id = a.associatedUserId

我尝试转换它，但SET上出现错误。这是我的查询语句：

UPDATE webusers 
SET (shares_this_round) = (a.id)
FROM (SELECT coalesce(count(s.id),0) AS id, p.associatedUserId FROM pool_worker p 
LEFT JOIN shares s ON p.username=s.username WHERE s.our_result='Y' GROUP BY p.associatedUserId) a, webusers w WHERE u.id = a.associatedUserId

有人能告诉我这是什么问题吗？我因为这个问题睡不着觉。

     ------------------------------EDIT-------------------------------------

股份表格

CREATE TABLE shares (
id bigint NOT NULL,
rem_host character varying(255) NOT NULL,
username character varying(120) NOT NULL,
our_result character(255) NOT NULL,
upstream_result character(255),
reason character varying(50),
solution character varying(1000) NOT NULL,
"time" timestamp without time zone DEFAULT now() NOT NULL
);

Web用户表

CREATE TABLE webusers (
id integer NOT NULL,
admin integer NOT NULL,
username character varying(40) NOT NULL,
pass character varying(255) NOT NULL,
email character varying(255) NOT NULL,
"emailAuthPin" character varying(10) NOT NULL,
secret character varying(10) NOT NULL,
"loggedIp" character varying(255) NOT NULL,
"sessionTimeoutStamp" integer NOT NULL,
"accountLocked" integer NOT NULL,
"accountFailedAttempts" integer NOT NULL,
pin character varying(255) NOT NULL,
share_count integer DEFAULT 0 NOT NULL,
stale_share_count integer DEFAULT 0 NOT NULL,
shares_this_round integer DEFAULT 0 NOT NULL,
api_key character varying(255),
"activeEmail" integer,
donate_percent character varying(11) DEFAULT '1'::character varying,
btc_lock character(255) DEFAULT '0'::bpchar NOT NULL
);

工作池表

CREATE TABLE pool_worker (
id integer NOT NULL,
"associatedUserId" integer NOT NULL,
username character(50),
password character(255),
allowed_hosts text
);

- Keshav Nair

@ErwinBrandstetter -非常抱歉，我已经更新了问题。 - Keshav Nair

+1 感谢您的跟进和更新。 - Craig Ringer

@CraigRinger - 没关系，只是我一直在解决这个问题并且正在开发比特币前端，所以没有太多时间甚至呼吸。 - Keshav Nair

加上表定义后，这个问题现在很好了。下次记得也要包括程序版本。大多数情况下是相关的。 - Erwin Brandstetter

我是的，我刚才差点晕过去了，所以没有太清晰地思考。 - Keshav Nair

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

首先，我进行了格式化，得到了这个不那么混乱但仍然不正确的查询：

UPDATE webusers 
SET   (shares_this_round) = (a.id)
FROM  (
   SELECT coalesce(count(s.id),0) AS id, p.associatedUserId
   FROM   pool_worker p 
   LEFT   JOIN shares s ON p.username=s.username
   WHERE  s.our_result='Y'
   GROUP  BY p.associatedUserId) a
   , webusers w
WHERE u.id = a.associatedUserId

这个声明中存在多个明显的错误和更多次优的部分。首先，需要加粗标出错误。最后几项是建议性内容。

Missing alias u for webuser. A trivial mistake.
Missing join between w and a. Results in a cross join, which hardly makes any sense and is a very expensive mistake as far as performance is concerned. It is also completely uncalled for, you can drop the redundant second instance of webuser from the query.
SET (shares_this_round) = (a.id) is a syntax error. You cannot wrap a column name in the SET clause in parenthesis. It would be pointless anyway, just like the parenthesis around a.id. The latter isn't a syntax error, though.
As it turns out after comments and question update, you created the table with double-quoted "CamelCase" identifiers (which I advise not to use, ever, for exactly the kind of problems we just ran into). Read the chapter Identifiers and Key Words in the manual to understand what went wrong. In short: non-standard identifiers (with upper-case letters or reserved words, ..) have to be double-quoted at all times.
I amended the query below to fit the new information.
The aggregate function count() never returns NULL by definition. COALESCE is pointless in this context. I quote the manual on aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected.

Emphasis mine. The count itself works, because NULL values are not counted, so you actually get 0 where no s.id is found.
I also use a different column alias (id_ct), because id for the count is just misleading.
WHERE s.our_result = 'Y' ... if our_result is of type boolean, like it seems it should be, you can simplify to just WHERE s.our_result. I am guessing here, because you did not provide the necessary table definition.
It is almost always a good idea to avoid UPDATEs that do not actually change anything (rare exceptions apply). I added a second WHERE clause to eliminate those:
```
AND   w.shares_this_round IS DISTINCT FROM a.id
```
If shares_this_round is defined NOT NULL, you can use <> instead because id_ct cannot be NULL. (Again, missing info in question.)
USING(username) is just a notational shortcut that can be used here.

将所有内容结合起来，得到这个正确的形式:

UPDATE webusers w
SET    shares_this_round = a.id_ct
FROM  (
   SELECT p."associatedUserId", count(s.id) AS id_ct
   FROM   pool_worker p 
   LEFT   JOIN shares s USING (username)
   WHERE  s.our_result = 'Y'                        -- boolean?
   GROUP  BY p."associatedUserId"
   ) a
WHERE w.id = a."associatedUserId"
AND   w.shares_this_round IS DISTINCT FROM a.id_ct  -- avoid empty updates