SQL команды

Выборка таблицы и вставка в соседнюю таблицу

  INSERT INTO modx_site_content (pagetitle,longtitle)
    SELECT pagetitle, longtitle
    FROM modx_site_content
    WHERE archive = 1

Выборка максимальной цены

  SELECT pagetitle, SUM(price)
    FROM modx_site_content WHERE id = 462
    GROUP BY archive

Массовая вставка значений

  UPDATE modx_tm2_tender_main SET rank = case id
    WHEN 2 THEN 5
    WHEN 3 THEN 4
  END
    WHERE id IN (2,3)

Не разобрал


INSERT INTO modx_tm2_tenders_cat (tid, org_id, region, archive, category_id) SELECT `tender`.`id`, `tender`.`org_id`, `org`.`region`,`tender`.`archive`, `mtCategoryCode`.`category_id` FROM `modx_tm2_category_code` AS `mtCategoryCode` LEFT JOIN `modx_tm2_lots` `lot` ON mtCategoryCode.option_id = lot.cid LEFT JOIN `modx_tm2_tenders` `tender` ON lot.tid = tender.id LEFT JOIN `modx_tm2_org` `org` ON tender.org_id = org.id WHERE  ( `tender`.`id` IS NOT NULL AND `mtCategoryCode`.`category_id` IN (410,411,412,413,414,415,416,417,420,421,422,423,429,430,431,432))

UPDATE modx_tm2_tenders_cat t1 SET price = (SELECT max_price FROM modx_tm2_tenders t2 WHERE t1.tid = t2.id and t2.archive = 1);
UPDATE modx_tm2_tenders_cat t1 SET active_price = (SELECT max_price FROM modx_tm2_tenders t2 WHERE t1.tid = t2.id and t2.archive = 0);

UPDATE modx_tm2_org t1 SET num = (SELECT num FROM modx_tm2_org_attributes t2 WHERE t1.id = t2.oid);

UPDATE modx_tm2_org t1 SET customer = (SELECT public FROM modx_tm2_tenders t2 WHERE t1.id = t2.org_id and public = 1);

INSERT INTO modx_tm2_tenders_cat (tid, org_id, name, region, archive, supplier, customer, building, category_id) SELECT `tender`.`id`, `tender`.`org_id`, `org`.`short_name`, `org`.`region`,`tender`.`archive`, `org`.`supplier`, `org`.`customer`, `org`.`building`, `mtCategoryCode`.`category_id` FROM `modx_tm2_category_code` AS `mtCategoryCode` LEFT JOIN `modx_tm2_lots` `lot` ON mtCategoryCode.option_id = lot.cid LEFT JOIN `modx_tm2_tenders` `tender` ON lot.tid = tender.id LEFT JOIN `modx_tm2_org` `org` ON tender.org_id = org.id WHERE  ( `tender`.`id` IS NOT NULL AND `mtCategoryCode`.`category_id` IN (57))

UPDATE DISTINCT org_id modx_tm2_tenders_cat t1 SET t1.default = 1 WHERE t1.org_id = (SELECT DISTINCT org_id FROM modx_tm2_tenders_cat t2);
UPDATE DISTINCT org_id modx_tm2_tenders_cat SET default = 1;

SELECT DISTINCT org_id FROM modx_tm2_tenders_cat t2

UPDATE modx_tm2_tenders_cat SET default = '1'  WHERE ( SELECT DISTINCT org_id FROM modx_tm2_tenders_cat );
UPDATE modx_tm2_tenders_cat t1 SET default = 1 WHERE org_id = (SELECT DISTINCT org_id FROM modx_tm2_tenders_cat LIMIT 1)

UPDATE modx_tm2_org t1 SET archive_price = (SELECT sum(max_price) FROM modx_tm2_tenders t2 WHERE t2.org_id = t1.id and t2.archive = 1);
UPDATE modx_tm2_org t1 SET active_price = (SELECT sum(max_price) FROM modx_tm2_tenders t2 WHERE t2.org_id = t1.id and t2.archive = 0);

SELECT SQL_CALC_FOUND_ROWS `tmOrganization`.`id`, `tmOrganization`.`full_name`, `tmOrganization`.`short_name`, `tmOrganization`.`region` FROM `modx_tm2_org` AS `tmOrganization` WHERE `tmOrganization`.`customer` = 1 GROUP BY tmOrganization.id ORDER BY short_name asc LIMIT 10
SELECT SQL_CALC_FOUND_ROWS `tmOrganization`.`id` FROM `modx_tm2_org` AS `tmOrganization` WHERE `tmOrganization`.`customer` = 1 GROUP BY tmOrganization.id ORDER BY short_name asc LIMIT 10
SELECT * from modx_tm2_address WHERE tid NOT IN (SELECT modx_tm2_address_tender.tid FROM modx_tm2_address_tender )
12 июня 2017, 16:58    168



Наверх