Update with a nested subquery (amount) to obtain a restriction on the update condition

Question

Got this bit of SQL as an update to the script, I tried to add a working round so as not to include the table that will be updated as a sentence in the statement, so using subqueries, but struggling to get this to work.

In essence, I need to update the vailue in table 1 with the sum of the fields in table 2, but only where the other two fields are the same across multiple tables and based on field6, the restriction applies to the update clause.

UPDATE table1 W SET Field1=(SELECT field2 FROM (SELECT A.id, B.field3, SUM(A.field2) AS field2 FROM table2 A, table3 B, table4 P WHERE A.id=B.id AND P.field6=B.field6) B ) WHERE W.field6=B.field6 

In a real-world example, select the amount of points missed in a rugby game when a rugby player participated in the match. table 2 contains the results (including assessment), table 3 contains the tables of the teams, and tables 1 and 4 are the same table of players that needs to be updated.

Hope this is clear enough and someone can point me in the right direction.

Tried the following:

  UPDATE $WSLKEEP W, $WSLFIX A, $WSLFIXPLAY B SET W.F_CONCEDED=SUM(A.F_AGAINST) WHERE A.F_ID=B.F_GAMEID AND B.F_NAME=W.F_NAME" 

but now stuck: Invalid use of group function

Yours faithfully

Answer 1

It seems that your subquery should be grouped on field6 and expose this column for internal join using table1 . Here's how you do it in MySQL :

 UPDATE table1 W INNER JOIN ( SELECT B.field6, SUM(A.field2) AS field2 FROM table2 A, table3 B, table4 P WHERE A.id=B.id AND P.field6=B.field6 GROUP BY B.field6 ) B ON W.field6=B.field6 SET W.Field1 = B.Field2 

And while we're on it, I also recommend that you refrain from (ab), using commas, in favor of explicit joins. The latter, however unusual it may be, ever accustomed to a different syntax, may very soon become familiar and much more intuitive than the former. Much has been said about this, and some people can hold rather strong opinions about joining a comma. I say that the comma can still use its share. However, when you join a condition, the current ANSI syntax should be your choice.

Here, the above statement with a subquery is converted to use explicit joins:

 UPDATE table1 W INNER JOIN ( SELECT B.field6, SUM(A.field2) AS field2 FROM table2 A INNER JOIN table3 B ON A.id = B.id INNER JOIN table4 P ON P.field6 = B.field6 GROUP BY B.field6 ) B ON W.field6 = B.field6 SET W.Field1 = B.Field2 

Answer 2

For an update request such as yours above, you are allowed to include several tables in the UPDATE clause, even if you do not update all of them. This will make unnecessary queries and speed up execution. For example, you can do something like this.

 UPDATE table1 W, table2 A, table3 B, table4 P SET W.Field1 = SUM(A.field2) ... 

I don’t quite understand the specifics of what you are trying to accurately update, but I just wanted to say that you can often avoid subqueries using this syntax.