CODEWITHSUNDEEP
mysqlselectsql-update
Jarosław Król
Jarosław Król

Reputation: 25

Update rows by (results from select) mysql

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'

(((RESULTS FROM SELECT))):

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

I would like to update some number which 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  |

Upvotes: 0

Views: 44

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 9048

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

Upvotes: 1

Related Questions