CODEWITHSUNDEEP
mysqljoinupdates
Alain BUFERNE
Alain BUFERNE

Reputation: 2071

Mysql Can I make an update with join

I have a database with several tables and all my queries work but I would like to had a new one and can't succeed to make it working. Two tables 'vins' and 'producteurs'with a foreign key IDproducteur in my 'vins' table. I need to update the 'vins'table from an excel source where the data are not always written corectly and need to change the stock value for some records of the 'vins' table.The p.nomproducteur and V.annee and v.nom are parameters but for test I put hard values. My Query is the following:

UPDATE vins AS v 
JOIN producteurs p  
ON ( p.IDproducteur = v.IDproducteur AND p.nomproducteur LIKE "Charles" )
SET stock = 10
WHERE v.nom LIKE "Symphonie" AND v.annee = 2013

I have two records where the producteur is "Charles" and V.nom is Symphonie one withe annee = 2010 and one with 2013. I got no error in phpMyadmin but there is no results to my Query even by changing some command order. Is what I want impossible to do?.

Upvotes: 0

Views: 26

Answers (2)

forpas
forpas

Reputation: 164174

Put the condition p.nomproducteur LIKE "Charles" in the WHERE clause:

UPDATE vins v 
JOIN producteurs p ON p.IDproducteur = v.IDproducteur 
SET stock = 10
WHERE 
  v.nom = "Symphonie" AND v.annee = 2013 AND p.nomproducteur = "Charles"

Also there is no need for LIKE in this case, a simple = will do.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133390

The update based on JOIN is commonly used in mysql so be sure that your join and where condition really match the values in your tables 

UPDATE vins AS v 
INNER JOIN producteurs p ON  p.IDproducteur = v.IDproducteur 
SET v.stock = 10
WHERE v.nom =  "Symphonie" 
AND v.annee = 2013 
AND p.nomproducteur = "Charles"

and do the fact you are not filter for partial string use = instead of like

Upvotes: 0

Related Questions