Update with subconsultation and Inner Join - Mysql

Question

Tables
Contract
id, contract, credit_id, debtor, evento_id, campanha_id

Title
id, contrato_id, title, maturity, value

The idea is to update the field campanha_id of contracts using a sub-allotment and Inner Join

The question is: How to update the campanha_id field of all contracts returned in the query ?

I built a complete example with query parameters on SQL Fiddle for better viewing of tables.

No update query:

SELECT c.id,
       c.contrato,
       t.valor_total
FROM   contrato c
       INNER JOIN(SELECT contrato_id,
                         Sum(valor) valor_total
                  FROM   titulo t
                  GROUP  BY contrato_id) t
               ON t.contrato_id = c.id
WHERE  c.credor_id = 2

Query that returns syntax error

UPDATE c
SET    c.campanha_id = 5
FROM   contrato AS c
       INNER JOIN (SELECT contrato_id,
                          Sum(valor) valor_total
                   FROM   titulo t
                   GROUP  BY contrato_id) t
               ON t.contrato_id = c.id
WHERE  c.credor_id = 2

Answer 1

The idea is to update the campanha_id field of contracts using a subconsulta and Inner Join

The question is: How to update the campanha_id field of all contracts returned in the query ?

Adjust your query by turning it into a UPDATE as follows:

UPDATE contrato c
       INNER JOIN(SELECT contrato_id,
                         Sum(valor) valor_total
                  FROM   titulo t
                  GROUP  BY contrato_id) t
               ON t.contrato_id = c.id
   SET c.campanha_id = 5               
 WHERE c.credor_id = 2

Note that the correct syntax of SET in charge UPDATE of Mysql is displayed after the junctions inner join.

I performed the command test on Sqlfiddle available this link to access the test in Sqlfiddle

I performed adjustment in the example provided in the Sqlfiddle of the question to be able to perform the test, because in the initial command of the author mentions the field campanha_id as a requirement of the response, but in the provided structure, the field was absent.

After executing the commands for creating the tables, inserting values and executing the UPDATE, the result of the following command:

Select * from contrato

Presented the Upshot:

id	contract	credit_id	debtor	evento_id	bell
1	1	1	123456	1	(null)
2	1	1	654321	2	(null)
3	2	2	654387	3	5

In response to Sqlfiddle of the question, it is also necessary to adapt the consultation SQL, turning it into a UPDATE as shown in this other example transformation of Query in Update.