Question 20762 in Mysql Question posted in
The official MySQL documentation can be found here.

Do I need a CURSOR for this? – Mysql

I am trying to step through a table and update an attribute based on the below query.

When I run the below, it set’s all values to 52, so it is recognising the first case and applying to all values. If I remove the count from the select statements of the case I get an error saying it returns more than 1 row.
Each of the select statements are correct in returning the items that need to be updated when I run them individually. For instance, the select statement in the first case has 41 rows (products) that need updating to value ’52’, then another 14 rows for the second case, 35 for the next and 26 for the next.

I am going around in circles and I have absolutely no idea how to apply the value to the rows based on each of the select statements in the cases. I don’t think a case statement is correct either now. Please help.

My thinking is along ‘for each row in select statement update value = x where t1.taxonomy= ‘pa_supplier and t1.product_id = table2.product_id where table2.sku = table4.VendorStockCode’. How to write this in mySQL though? Stumped…

UPDATE table1 t1
SET t1.term_id = 
(
    CASE
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty <= 0
            ) > 0
        THEN '52'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable <= 0 AND t5.Stock_qty > 0
            ) > 0
        THEN '51'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) > cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '51'
                WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) <= cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '52'
        ELSE t1.term_id
    END
)
WHERE t1.taxonomy= 'pa_supplier';
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally got the below to work but takes a very long time to execute, but it is working.

    UPDATE table1 t1
SET t1.term_id = 52 WHERE t1.taxonomy= 'pa_supplier' AND t1.product_id IN (
SELECT product_id FROM table2 t2
INNER JOIN table3 t3 ON t3.Manufacture_Code = t2.sku
INNER JOIN table4 t4 ON t4.VendorStockCode= t3.Manufacture_Code AND t4.StockAvailable > 0 AND t3.Stock_qty <= 0)

UPDATE table1 t1
SET t1.term_id = 51 WHERE t1.taxonomy= 'pa_supplier' AND t1.product_id IN (
SELECT product_id FROM table2 t2
INNER JOIN table3 t3 ON t3.Manufacture_Code = t2.sku
INNER JOIN table4 t4 ON t4.VendorStockCode= t3.Manufacture_Code AND t4.StockAvailable <= 0 AND t3.Stock_qty > 0)            

UPDATE table1 t1
SET t1.term_id = 51 WHERE t1.taxonomy= 'pa_supplier' AND t1.product_id IN (
SELECT product_id FROM table2 t2
INNER JOIN table3 t3 ON t3.Manufacture_Code = t2.sku
INNER JOIN table4 t4 ON t4.VendorStockCode= t3.Manufacture_Code AND t4.StockAvailable > 0 AND t3.Stock_qty > 0 AND CAST(DealerEx as decimal(10,2)) > cast(ExTax as decimal(10,2)))

UPDATE table1 t1
SET t1.term_id = 52 WHERE t1.taxonomy= 'pa_supplier' AND t1.product_id IN (
SELECT product_id FROM table2 t2
INNER JOIN table3 t3 ON t3.Manufacture_Code = t2.sku
INNER JOIN table4 t4 ON t4.VendorStockCode= t3.Manufacture_Code AND t4.StockAvailable > 0 AND t3.Stock_qty > 0 AND CAST(DealerEx as decimal(10,2)) <= cast(ExTax as decimal(10,2)))

  2. 0

    Use a CTE

    Try the query below it might need some syntax fixes:

    WITH
  trow AS (
Select * from table1 t1,
WHERE t1.taxonomy= 'pa_supplier')
UPDATE table1 t1
SET t1.term_id = 
(
    CASE
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty <= 0
            ) > 0
        THEN '52'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable <= 0 AND t5.Stock_qty > 0
            ) > 0
        THEN '51'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) > cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '51'
                WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) <= cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '52'
            ELSE t1.term_id
        END
    )
INNER JOIN table1 as t1
on trow.product_id=t1.product_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search