UPDATE values from one table using values from another table-CodePudding

I have two tables mysql with the info:

TABLE A

torneo	equipo	importe
3	1	100.00
3	2	200.00

TABLE B

equipo	torneo	importe	deuda	pago
1	3	20.00	20.00	0
2	3	300.00	300.00	1

And I need know how to update the value from IMPORTE on TABLE B only if PAGO column on TABLE B are value 0 with the content of TABLE A column IMPORTE where equipo and torneo from both tables are the same.

Any idea?

CodePudding user response：

We can use an update join here:

UPDATE TableB b
INNER JOIN TableA a
    ON a.torneo = b.torneo AND
       a.equipo = b.equipo
SET b.importe = a.importe
WHERE b.pago = 0;

CodePudding user response：

I used this solution.

UPDATE `TableB`AS b
INNER JOIN `TableA` AS a
ON  a.torneo = b.torneo AND
    a.equipo = b.equipo
SET b.importe = a.importe
WHERE b.pagos = 0;