My SQL sproc with an update

Question

I have a stored procedure that is used when a new transaction is inserted. This procedure inserts properly into the transactions table but I also need to update another related table based on inserted values. Based on the Product_ID I need to update PT_Pct_to_Salon in a table called 'Salon' with a value from a table called 'Zen_Products_Description'. The related salon can be found using the 'Salon_ID' from insert which is equivalent to the 'Salon' table's PK ID. The value I need to insert is in the 'web_share' field of the 'Zen_Products_Description' table. The related row in 'Zen_Products_Description' can be matched by matching the inserted value 'Product_ID' with the PK of 'Zen_Products_Description' which is called 'products_id'. I am using MySQL 5.

---

BEGIN INSERT INTO Transactions (Cart_Trans_ID, Customer_ID, Pass_Through_Amt, Product_ID, Product_Name, Product_Qty, Salon_ID, Stylist_ID, Trans_Type, customerAddress, customerCity, customerEmail, customerFirstName, customerLastName, customerPhone, customerPostal, customerState) VALUES (Cart_Trans_ID, Customer_ID, Pass_Through_Amt, Product_ID, Product_Name, Product_Qty, Salon_ID, Stylist_ID, Trans_Type, customerAddress, customerCity, customerEmail, customerFirstName, customerLastName, customerPhone, customerPostal, customerState); Insert Into Zen_Products_Description (products_id, products_name) Values (Product_ID, Product_Name) ON DUPLICATE KEY UPDATE products_name = Product_Name;

//this is where I try unsuccessfully to update update Salon set PT_Pct_to_Salon = Zen_Products_Description.web_share join Salon on Salon.Salon_ID = Transactions.Salon_ID join Zen_Products_Description on Zen_Products_Description.products_id = Transactions.Product_ID;

END

---

Answer 1

lol - you forgot to ask a question... be succinct and i'll help you

BEGIN 
  INSERT INTO Transactions 
    (Cart_Trans_ID, Customer_ID, 
     Pass_Through_Amt, Product_ID, Product_Name, 
     Product_Qty, Salon_ID, Stylist_ID, 
     Trans_Type, customerAddress, customerCity, 
     customerEmail, customerFirstName, customerLastName, 
     customerPhone, customerPostal, customerState) 
  VALUES 
    (Cart_Trans_ID, Customer_ID, 
     Pass_Through_Amt, Product_ID, Product_Name, 
     Product_Qty, Salon_ID, Stylist_ID, 
     Trans_Type, customerAddress, customerCity, 
     customerEmail, customerFirstName, customerLastName, 
     customerPhone, customerPostal, customerState); 


Insert Into Zen_Products_Description 
  (products_id, products_name) 
Values 
  (Product_ID, Product_Name) 

ON DUPLICATE KEY 

UPDATE products_name = Product_Name

########## everything above here is immaterial

update Salon 
  set PT_Pct_to_Salon = Zen_Products_Description.web_share 
join Salon on Salon.Salon_ID = Transactions.Salon_ID 
join Zen_Products_Description 
  on Zen_Products_Description.products_id = Transactions.Product_ID;

############### here is update

UPDATE salon A 
INNER JOIN Transactions B ON A.salon_ID = B.salon_ID
INNER JOIN Zen_Products_Description C on C.Products_id = B.product_id
SET A.PT_Pct_to_Salon = C.web_share

## , a.x = b.x etc... ## and by the way - learn how to format your code so people can read it...

END

Answer 2

Okay, thanks; too involved in trying to clearly explain the problem to be specific about the question. My question is: how do I update Salon.PT_Pct_to_Salon in the related salon from Zen_Products_Description.web_share for the related product? The part right below the comment line is where I tried unsuccessfully to make it work. Maybe I took the wrong direction.

Answer 3

That worked and I thank you very much for your help!