phpmyadmin Question posted in
The official documentation can be found here.

Mysql Can I make an update with join – Phpmyadmin

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?.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search