Update rows by (results from select) mysql-CodePudding

is there anybody who can help me to update all the rows by results from select?:

UPDATE newser SET total_rating = total_rating   (((RESULTS FROM SELECT))) WHERE 
    bdate >= NOW() - INTERVAL 12 HOUR AND 
    usertext ='maxer12'
enter code here

(((RESULTS FROM SELECT))):

SELECT DATE_FORMAT(`bdate`, '%i') FROM newser

I would like to update some number whitch exists in total_rating column, by adding to them results from (((RESULTS FROM SELECT))

So it should looks like this:

 UPDATE newser SET total_rating = total_rating   (SELECT DATE_FORMAT(`bdate`, '%i') FROM newser) WHERE 
        bdate >= NOW() - INTERVAL 12 HOUR AND 
        usertext ='maxer12'

Is this correct way to achieve this? Any help really appreciated.

TABLE before update:

| id | date| total_result | username |
| 1  |   4 |       3      | maxer12  |
| 2  |   4 |       6      | maxer12  |
| 3  |   5 |       5      | maxer12  |
| 4  |   5 |       4      | maxer12  |
| 5  |  33 |       3      | maxer12  |

TABLE after update: (date total_result ) in the same table:

| id | date| total_result | username |
| 1  |   4 |       7      | maxer12  |
| 2  |   4 |       10     | maxer12  |
| 3  |   5 |       10     | maxer12  |
| 4  |   5 |       9      | maxer12  |
| 5  |  33 |       36     | maxer12  |

CodePudding user response：

You could use INNER JOIN. I supposed id is unique, you may have some other column to make the join condition. Keep in mind that joins in almost all cases perform faster than subqueries. And you can not use limit when you make an update with join.

UPDATE newser  n 
INNER JOIN
( 
   SELECT id, DATE_FORMAT(`bdate`, '%i') as total_rating1  
   FROM newser 
) as n1
on n.id=n1.id
SET n.total_rating = n.total_rating   n1.total_rating
WHERE  bdate >= NOW() - INTERVAL 12 HOUR AND usertext ='maxer12';