Update via another table - Mysql

Asked

Viewed 73 times

0

I have the following problem: I have a table rev_tasks which contains id as key, and a second table rev_reviews and has as a foreign key task_id, pointing to the task, in addition I have a field (revised) whether the review has been reviewed or not.

In the rev_task I have a field (totalNRev) indicating how many views NAY were revised

at this time I’m having problems, I have to update the database for all tasks updating the totalNRev field.

Could you give me suggestions ? Thanks in advance.

mysql sql sql-update

  • update knife using Join

  • Hello Victor, could you be more clear? the Join would be on set or on Where ?

  • do not understand... that you want to update the field totalNRev in the table rev_task right ? Or fields from two different tables at the same time, in this case could use Join

  • that’s right, this field is the count of all the rev_reviews where the revised = 'Nao', in addition to this condition I have to update only the rev_tasks that the id is equal to the task_id of the rev_reviews table, I could see there ?

  • I will post an answer to Voce check if it meets your need, if yes comment below the answer

  • Hello Victor, I managed to solve the problem, I put as a response my resolution, thank you//

  • ok I will remove my answer then

Show 2 more comments

1 answer

0

So guys, I managed to solve here through this update

update rev_tasks t inner join (select task_id, count(1) as qtd from rev_reviews where revisado = 'Nao' group by task_id) r on t.id = r.task_id set t.totalPen = r.qtd where t.id = r.task_id;

Browser other questions tagged mysql sql sql-update

You are not signed in. Login or sign up in order to post.