My SQL stored procedure with update
I have a stored procedure that is used when inserting a new transaction. This procedure is correctly inserted into the transaction table, but I also need to update another related table based on the inserted values.
Based on Product_ID, I need to update PT_Pct_to_Salon in the table called "Salon" with the value from the table called "Zen_Products_Description". The associated salon can be found using the "Salon_ID" from the insert, which is equivalent to the "Salon" PK table.
The value I need to insert is in the "web_share" field of the "Zen_Products_Description" table. The corresponding string in "Zen_Products_Description" can be matched by matching the inserted "Product_ID" value to the PK "Zen_Products_Description" 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
lol - you forgot to ask a question ... be brief and I will 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
########## all of the above is intangible
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
END
source to share