Mysql update error-CodePudding

 
The UPDATE pass_activity SET overall_sort=(10 - STATUS) * 100000000 + instance_number + (SELECT 
N.n um FROM (SELECT (CASE WHEN vip_rank=1 AND vip_expdate & gt; NOW (), THEN 100 
The WHEN vip_rank=2 AND vip_expdate & gt; NOW (), THEN 200 
The WHEN vip_rank=3 AND vip_expdate & gt; NOW (), THEN 300 ELSE 0 END) 
+ (CASE WHEN ue. Identity_verified IS NOT NULL THEN 50 ELSE 0 END) 
AS num 
The FROM accuvally_user u JOIN accuvally_user_extends ue ON u.a ccount=ue. The account WHERE u.a ccount.=pass_activity create_by) AS n) 
WHERE id IN (5395653487500, 4394645338900739945831, 00134275241, 00)

Pass_activity table overall_sort field needs according to accuvally_user table (vip_rank vip_expdate 2 column) and accuvally_user_extends table (identity_verified a field), through the user id to calculate the corresponding order value, I this kind of writing has a problem, an error:
1 the queries executed, 0 success, 1 errors, warnings 0

Query: the UPDATE pass_activity SET overall_sort=(10 - status) * 100000000 + instance_number + (SELECT n.n um FROM (SELECT (CASE WHEN a VIP...

Error code: 1054
Unknown column 'pass_activity. Id' 'in the where clause'

How can I change?

CodePudding user response:

 UPDATE pass_activity a JOIN (SELECT pa. Id (CASE WHEN vip_rank=1 AND vip_expdate & gt; NOW (), THEN 100 
The WHEN vip_rank=2 AND vip_expdate & gt; NOW (), THEN 200 
The WHEN vip_rank=3 AND vip_expdate & gt; NOW (), THEN 300 ELSE 0 END) 
+ (CASE WHEN ue. Identity_verified IS NOT NULL THEN 50 ELSE 0 END) 
AS num 
The FROM accuvally_user u JOIN accuvally_user_extends ue ON u.a ccount=ue. The account JOIN pass_activity pa ON u.a ccount=pa. Create_by) AS n 
ON Anderson, d=n.i d 
The SET overall_sort=(10 - STATUS) * 100000000 + instance_number + n.n um 
WHERE Anderson d IN (5395653487500, 4394645338900739945831, 00134275241, 00)

Just myself, I should be, and there is no other way?

CodePudding user response:

 
The UPDATE pass_activity 
The SET overall_sort=(10 - STATUS) * 100000000 + instance_number 
+ (SELECT (CASE WHEN vip_rank=1 
AND vip_expdate & gt; NOW (), THEN 100 
The WHEN vip_rank=2 
AND vip_expdate & gt; NOW (), THEN 200 
The WHEN vip_rank=3 
AND vip_expdate & gt; NOW (), THEN 300 
The ELSE 0 
END) 
+ (CASE WHEN ue. Identity_verified IS NOT NULL THEN 50 
The ELSE 0 
END) AS num 
The FROM accuvally_user u 
The JOIN accuvally_user_extends ue ON u.a ccount=ue. The account 
WHERE u.a ccount=pass_activity. Create_by 
) 
WHERE id IN (5395653487500, 4394645338900, 7393945083100, 1343275124100);

CodePudding user response:

No other way, the UPDATE of the MYSQL support only UPDATE a inner join on b... The set...