mysql - MySQL 查询的 PostgreSQL UPDATE 等价物

Question

我有一个简单的 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
);

网络用户表

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
);

pool_workes 表

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

Answer 1

首先，我格式化以得出这个不那么令人困惑但仍然不正确的查询:

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

此语句中存在多个明显的错误和更多次优部分。错误排在第一位，并带有大胆强调。最后几项只是建议。

1. 缺少别名 u对于网络用户。一个微不足道的错误。

2. w 之间缺少连接和 a .导致交叉连接，这几乎没有任何意义，并且就性能而言是一个非常昂贵的错误。这也是完全不需要的，你可以删除webuser的冗余第二个实例| 来自查询。

3. SET (shares_this_round) = (a.id)是一个语法错误。您不能将列名称包装在 SET 中括号中的子句。无论如何这都是没有意义的，就像 a.id 周围的括号一样.不过，后者不是语法错误。

4. 在评论和问题更新后，您创建了带有双引号 "CamelCase" 的表格标识符(对于我们刚刚遇到的那种问题，我建议永远不要使用它)。阅读章节Identifiers and Key Words在手册中了解出了什么问题。简而言之:非标准标识符(带有大写字母或保留字，..)必须始终用双引号引起来。
   我修改了下面的查询以适应新信息。

5. 聚合函数count()永不返回 NULL根据定义。 COALESCE在这种情况下毫无意义。我引用 the manual on aggregate functions :

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

   强调我的。计数本身有效，因为 NULL值不计算在内，所以你实际上得到 0 没有 s.id找到了。

6. 我还使用了不同的列别名 ( id_ct )，因为 id因为计数只是误导。

7. WHERE s.our_result = 'Y' ... 如果 our_result类型为 boolean ，就像看起来应该的那样，您可以简化为 WHERE s.our_result .我在这里猜测，因为您没有提供必要的表定义。

8. 避免实际上不改变任何东西的更新几乎总是一个好主意(极少数异常(exception)情况适用)。我添加了第二个 WHERE消除那些的条款:

   AND   w.shares_this_round IS DISTINCT FROM a.id
   

   如果shares_this_round定义 NOT NULL , 你可以使用 <>相反因为 id_ct不能是 NULL . (同样，缺少相关信息。)

9. USING(username)只是可以在这里使用的符号快捷方式。

将所有内容放在一起以达到此正确形式:

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