mysql更新 跨 表,MySQL跨表更新

I have a users table and a sales table.

The users table has columns id and annual_plan_length, monthly_plan_length.

Example user row:

id | annual_plan_length | monthly_plan_length

55 | 0 | 0

The sales table has columns id, user_id, product

Example sales rows:

id | user_id | product

1 | 55 | monthly

2 | 55 | annually

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

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.: I know PDO is better but it's not used yet for this client's project.

Ps.: Syntax error near 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.

You need to remove GROUP BY .

Maybe it help you:

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

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