MySQL跨表更新 [英] MySQL cross-table update

问题描述

我有一个users表和一个sales表.

users表具有列id和annual_plan_length，monthly_plan_length.

用户行示例:

id | annual_plan_length | monthly_plan_length

55 | 0 | 0

sales表具有列id，user_id，product

示例sales行:

id | user_id | product

1 | 55 | monthly

2 | 55 | annually

我正在尝试编写一个查询，以根据用户使用sales表进行的购买次数来更新用户计划长度列.

I am trying to write a query to update the users plan length column according to how many purchases they've made using the sales table.

这是我到目前为止所得到的:

Here's what I got so far:

function updateLength($product, $column) {
  global $emdb;

  mysql_query("UPDATE users
            JOIN sales
            ON sales.user_id = users.id
            SET users.`".mysql_real_escape_string($column)."` = count(sales.id) GROUP BY sales.user_id
            WHERE sales.product = '".mysql_real_escape_string($product)."'", $emdb);

}

updateLength('monthly', 'annual_plan_length');
updateLength('annually', 'monthly_plan_length');

此查询不起作用，但是在您的帮助下，最终应该在用户ID 55的monthly_plan_length和annual_plan_length列上输入1.

This query doesn't work, but with your help, this should end up putting a 1 on the columns monthly_plan_length and annual_plan_length for the user ID 55.

Ps .:我知道PDO更好，但尚未用于该客户的项目. 提示:GROUP BY

Ps.: I know PDO is better but it's not used yet for this client's project. Ps.: Syntax error near GROUP BY

推荐答案

在查询结束时只能使用GROUP BY.我在您的查询中看到一个问题，因为您想更新具有相同产品但购买价值不同的每个用户.

You only can use GROUP BY in end your query. I see a problem on your query because you want to update every user that have a same product but with diferent values of purchases.

您需要删除GROUP BY.

也许对您有帮助

UPDATE users JOIN sales ON sales.user_id = users.id SET users.annual_plan_length = (SELECT count(user_id) FROM sales WHERE user_id = 1 WHERE sales.product = 'monthly');

UPDATE users JOIN sales ON sales.user_id = users.id SET users.annual_plan_length = (SELECT count(user_id) FROM sales WHERE user_id = 1 WHERE sales.product = 'monthly');

因为编辑应为6个字符，所以我做了以下操作:我将括号放在正确的位置.

Because the edit should be 6 characters, here's what I did: I put the paren at the right place.

这篇关于MySQL跨表更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！