Questions about MySQL UPDATE.-CodePudding

Recently in the "advanced SQL tutorial", in 1 to 2 since the connection usage section of a problems in the problem sets, specific as follows:
Exercises in 1-2-3, has the following form:

 
/* exercises: 1-2-3 update minor */
The CREATE TABLE DistrictProducts2 
(district VARCHAR (16) NOT NULL, 
The name VARCHAR (16) NOT NULL, 
Price INTEGER NOT NULL, 
Ranking INTEGER, 
PRIMARY KEY (district, name)); 

INSERT INTO DistrictProducts2 VALUES (' northeast ', 'orange', 100, NULL); 
INSERT INTO DistrictProducts2 VALUES (' northeast ', 'apple', 50, NULL); 
INSERT INTO DistrictProducts2 VALUES (' northeast ', 'grapes', 50, NULL); 
INSERT INTO DistrictProducts2 VALUES (' northeast ', 'lemon', 30, NULL); 
INSERT INTO DistrictProducts2 VALUES (' the kanto ', 'lemon', 100, NULL); 
INSERT INTO DistrictProducts2 VALUES (' the kanto ', 'pineapple', 100, NULL); 
INSERT INTO DistrictProducts2 VALUES (' the kanto ', 'apple', 100, NULL); 
INSERT INTO DistrictProducts2 VALUES (' the kanto ', 'grapes', 70, NULL); 
INSERT INTO DistrictProducts2 VALUES (' kansai ', 'lemon', 70, NULL); 
INSERT INTO DistrictProducts2 VALUES (' kansai ', 'watermelon', 30, NULL); 
INSERT INTO DistrictProducts2 VALUES (' kansai ', 'apple', 20, NULL);

Topic request to insert group after Ranking, Ranking list
I write statement is as follows:

 
The UPDATE districtproducts2 
The SET ranking=
SELECT count (d2. ` name `) + 1 as rank1 
The from districtproducts2 as d1 left JOIN districtproducts2 as d2 
On d1. District=d2. District and d1. Price & lt; D2. Price 
GROUP BY d1. District, d1. Name 
The ORDER BY d1. District, rank1

Result error:

 
 1064 - You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count (d2. ` name `) + 1 as rank1 
The from districtproducts2 as d1 left JOIN dis' at line 3

Excuse me each bosses, why will appear this kind of circumstance, how to modify the statement?

CodePudding user response:

Select statement to add a parenthesis

CodePudding user response:

Thank you bosses reply,

 
The UPDATE districtproducts2 
The SET ranking=
(SELECT count (d2. ` name `) + 1 as rank1 
The from districtproducts2 as d1 left JOIN districtproducts2 as d2 
On d1. District=d2. District and d1. Price & lt; D2. Price 
GROUP BY d1. District, d1. Name 
The ORDER BY d1. District, rank1)

Is this right,
But to the wrong

 
You can 't specify target table' districtproducts2 'for update in the FROM clause

I checked the online this mistake, say need to SELECT it again:

 
The UPDATE districtproducts2 
The SET ranking=
(
The SELECT t.r ank1 FROM 
(SELECT count (d2. ` name `) + 1 as rank1 
The from districtproducts2 as d1 left JOIN districtproducts2 as d2 
On d1. District=d2. District and d1. Price & lt; D2. Price 
GROUP BY d1. District, d1. Name 
The ORDER BY d1. District, rank1) as t 
)

So there is another new error:

 
1242 - Subquery returns more than 1 row

Aiming at this kind of mistake, some people say that home limit 1 line, but this is not the original want?

CodePudding user response:

reference 1st floor evanweng response:

select statement to add a brackets

Thank you bosses reply, I am not good reference, reply on the first floor,

CodePudding user response:

This statement logic has a problem, do you want to achieve the effect of grouping by district, then the current record rank within the group

CodePudding user response:

 update districtproducts2 d set d.r anking=(
Select count (1) + 1 from (
The select d1. * from districtproducts2 d1 
D2) 
Where d2. District=d.d istrict 
And d2. Price & gt; D.p rice 
)

CodePudding user response:

The UPDATE districtproducts2 a, (SELECT d1 district, d1. Name, d1. Price, COUNT (d2. ` name `) + 1 AS rank1
The FROM districtproducts2 AS d1 LEFT JOIN districtproducts2 AS d2
ON d1. District=d2. District AND d1. Price & lt; D2. Price
GROUP BY d1. District, d1. Name
The ORDER BY d1. District, rank1) b SET a.r anking=b.r ank1
WHERE a. d. istrict=b.d istrict AND a.n ame=b.n ame AND Amy polumbo rice=p. rice;

CodePudding user response:

Give me first modify the SQL, if you didn't get sort error:

 
The UPDATE districtproducts2 
LEFT the JOIN (
SELECT 
Count (d2. ` name `) + 1 AS rank1, 
D1. ` name `, 
D1. District 
The FROM 
Districtproducts2 AS d1 
LEFT the JOIN districtproducts2 AS ON d1, d2 district=d2. District 
AND d1. Price & lt; D2. Price 
GROUP BY 
D1. District, 
D1. ` name ` 
The ORDER BY 
D1. District, 
Rank1 
) AS ON t (districtproducts2. ` name `=t. ` name ` AND districtproducts2. District=t.d istrict) 
The SET ranking=t.r ank1

To create a temporary table t, attribute is the name, district, rank1, after modify the corresponding item in the current table districtproducts2 ok