За последние 24 часа нас посетил 18841 программист и 1606 роботов. Сейчас ищут 608 программистов ...

Обновить таблицу по шаблону: update table1 table2 array where name in array

Тема в разделе "MySQL", создана пользователем RustHrust, 2 мар 2024.

Метки:
  1. RustHrust

    RustHrust Новичок

    С нами с:
    3 фев 2022
    Сообщения:
    5
    Симпатии:
    0
    Подскажите пожалуйста, вот такая ситуация: нужно обновить две таблицы массивом данных с условием где одно из полей таблицы содержит массив совпадений, делаю вот как:
    PHP:
    1. foreach ($daichi_product['Result'] as $item) {
    2.                 if($item['STORE']['NAME'] == 'Екатеринбург'){
    3.                     foreach($productName3 as $val){
    4.                         if($item['NAME'] == $val){
    5.                             $priceCountDaichi[] = "`aventa_products`.`price` = '".$item['PRICES']['mprc']['PRICE']."', `aventa_pp_availability`.`count` = '".$item['STORE']['STORE_AMOUNT']."'";
    6.                         }
    7.                     }
    8.                 }
    9.             }
    10.             $updateFieldsPriceCount = implode (', ', $priceCountDaichi);
    11.  
    12.             $updatePriceProductDaichi = "UPDATE `aventa_products` JOIN `aventa_pp_availability` ON `aventa_products`.`id` = `aventa_pp_availability`.`product_id` SET $updateFieldsPriceCount WHERE `aventa_products`.`name` IN ($sqlPartDaichi2)";
    13.             //db()->query($updatePriceProductDaici);
    14.             echo $updatePriceProductDaichi;
    Вот что получается на выходе:
    Код (Text):
    1. UPDATE `aventa_products` JOIN `aventa_pp_availability` ON `aventa_products`.`id` = `aventa_pp_availability`.`product_id` SET `aventa_products`.`price` = '56750.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '158400.00', `aventa_pp_availability`.`count` = '2', `aventa_products`.`price` = '175100.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '145990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '46300.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '46300.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '46300.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '86990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '33990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '34990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '31990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '37990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '79990.00', `aventa_pp_availability`.`count` = '2', `aventa_products`.`price` = '60490.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '38990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '32990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '170900.00', `aventa_pp_availability`.`count` = '3', `aventa_products`.`price` = '106900.00', `aventa_pp_availability`.`count` = '4', `aventa_products`.`price` = '106900.00', `aventa_pp_availability`.`count` = '4', `aventa_products`.`price` = '108400.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '180400.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '26990.00', `aventa_pp_availability`.`count` = '17', `aventa_products`.`price` = '29990.00', `aventa_pp_availability`.`count` = '50', `aventa_products`.`price` = '29990.00', `aventa_pp_availability`.`count` = '50', `aventa_products`.`price` = '37990.00', `aventa_pp_availability`.`count` = '34', `aventa_products`.`price` = '38490.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '101490.00', `aventa_pp_availability`.`count` = '8', `aventa_products`.`price` = '28990.00', `aventa_pp_availability`.`count` = '36', `aventa_products`.`price` = '42490.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '60990.00', `aventa_pp_availability`.`count` = '2', `aventa_products`.`price` = '51990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '30990.00', `aventa_pp_availability`.`count` = '47', `aventa_products`.`price` = '77990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '38990.00', `aventa_pp_availability`.`count` = '2', `aventa_products`.`price` = '59990.00', `aventa_pp_availability`.`count` = '1', `aventa_products`.`price` = '99990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '27490.00', `aventa_pp_availability`.`count` = '45', `aventa_products`.`price` = '50490.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '31990.00', `aventa_pp_availability`.`count` = '39', `aventa_products`.`price` = '28990.00', `aventa_pp_availability`.`count` = '17', `aventa_products`.`price` = '141500.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '186300.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '35290.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '173300.00', `aventa_pp_availability`.`count` = '5', `aventa_products`.`price` = '23990.00', `aventa_pp_availability`.`count` = '13', `aventa_products`.`price` = '144400.00', `aventa_pp_availability`.`count` = '4', `aventa_products`.`price` = '26490.00', `aventa_pp_availability`.`count` = '9', `aventa_products`.`price` = '48990.00', `aventa_pp_availability`.`count` = '18', `aventa_products`.`price` = '85800.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '198600.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '22490.00', `aventa_pp_availability`.`count` = '21', `aventa_products`.`price` = '199990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '92990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '23990.00', `aventa_pp_availability`.`count` = '11', `aventa_products`.`price` = '30490.00', `aventa_pp_availability`.`count` = '10', `aventa_products`.`price` = '46490.00', `aventa_pp_availability`.`count` = '27', `aventa_products`.`price` = '51990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '82490.00', `aventa_pp_availability`.`count` = '1', `aventa_products`.`price` = '107990.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '155990.00', `aventa_pp_availability`.`count` = '2', `aventa_products`.`price` = '30490.00', `aventa_pp_availability`.`count` = '22', `aventa_products`.`price` = '32990.00', `aventa_pp_availability`.`count` = '1', `aventa_products`.`price` = '41990.00', `aventa_pp_availability`.`count` = '29', `aventa_products`.`price` = '40490.00', `aventa_pp_availability`.`count` = '0', `aventa_products`.`price` = '42490.00', `aventa_pp_availability`.`count` = '35', `aventa_products`.`price` = '48490.00', `aventa_pp_availability`.`count` = '1' WHERE `aventa_products`.`name` IN ('FTYN25L/RYN25L/-40','FTYN25L/RYN25L/-40','FTYN35L/RYN35L/-40','FTYN50L/RYN50L/-40','FTYN60L/RYN60L/-40','Nobby Smart 24-2CSF','Kobold S-05','Kobold S-06','KSHE176HFAN3/KSUT176HFAN3','KSKS70HFAN1/KSUT70HFAN1','DA20AVQS1-S/DF20AVS1','DA25AVQS1-S/DF25AVS1','DA35AVQS1-S/DF35AVS1','DA50AVQS1-S/DF50AVS1','DA20AVQS1-W/DF20AVS1','DA25AVQS1-W/DF25AVS1','DA35AVQS1-W/DF35AVS1','DA60AVQS1-W/DF60AVS1','FTXF60A/RXF60B','MC55W','MCK55W','ICE35AVQ1/ICE35FV1','AIR35AVQS1R/AIR35FVS1R','KSGA26HZRN1/KSRA26HZRN1','KSGA53HZRN1/KSRA53HZRN1','KSGA70HZRN1/KSRA70HZRN1','KSGA21HFAN1/KSRA21HFAN1','KSGA26HFAN1/KSRA26HFAN1','KSGA35HFAN1/KSRA35HFAN1','KSGA53HFAN1/KSRA53HFAN1','KSGI21HFAN1/KSRI21HFAN1','KSGI26HFAN1/KSRI26HFAN1','KSGI26HFAN1/KSRI26HFAN1','MSAG1-07HRN1-I/MSAG1-07HRN1-O','MSAG1-09HRN1-I/MSAG1-09HRN1-O','MSAG1-12HRN1-I/MSAG1-12HRN1-O','MSAG1-18HRN1-I/MSAG1-18HRN1-O','MSAG1-12N8C2-I/MSAG1-12N8C2-O','MSAG1-24N8D0-I/MSAG1-24N8D0-O','FTXF25C/RXF25C','KSGI21HFAN1/KSRI21HFAN1/-40','ASX12D1/ASB12D1','ASX07DZ1R/ASB07DZ1R','ASX12DZ1R/ASB12DZ1R','AIR20AVQ1/AIR20FV1','AIR25AVQ1/AIR25FV1','AIR50AVQ1/AIR50FV1','ASX09D1Z1R/ASB09D1Z1R','Nobby Balance Plus 12-2CS','Nobby Balance Plus 18-2CS','Nobby Balance Plus 24-2CS','KSZTA53HFAN1/KSUTA53HFAN1/KPU65-D','KSVT105HFAN3/KSUR105HFAN3/KPU95-DR','KSVT140HFAN3/KSUT140HFAN3/KPU95-DR','KSVT176HFAN3/KSUT176HFAN3/KPU95-DR','KSGAA21HZRN1/KSRAA21HZRN1','KSGAA26HZRN1/KSRAA26HZRN1','KSGAA35HZRN1/KSRAA35HZRN1','KSGTI26HZRN1/KSRTI26HZRN1','KSGTI35HZRN1/KSRTI35HZRN1','KSGTI50HZRN1/KSRTI50HZRN1','KSGTI70HZRN1/KSRTI70HZRN1','KSGTI100HZAN1/KSRTI100HZAN1','KSGTI21HFAN1/KSRTI21HFAN1','KSGTI26HFAN1/KSRTI26HFAN1','KSGTI35HFAN1/KSRTI35HFAN1')
    Но во всех товарах обновляет только последнюю цену и кол-во: aventa_products.price = '48490.00', aventa_pp_availability.count = '1' Что я пытаюсь сделать - я хочу вне цикла foreach делать обновление таблицы, чтобы повысить скорость запроса, но вот как это сделать?
    Да, и еще, база данных - 5.5.51-MariaDB.
     
  2. Aleksandr.B

    Aleksandr.B Новичок

    С нами с:
    2 фев 2023
    Сообщения:
    158
    Симпатии:
    41
    Адрес:
    Барнаул
    Нужно формировать запрос update с использованием case, when, then, else.