mysql - 如何减少查询次数

Question

我正在尝试生成一个 catalog 表，这样每次我添加到其他 2 个表时，它都将是相同的 id。目前，我必须运行多个查询才能生成。只想尝试简化它。这是我的原始查询，欢迎任何合并想法!

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = '32g bins')
where item_name = '32g bins';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Collection Bags')
where item_name = 'Collection Bags';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Chicken Wire Bins')
where item_name = 'Chicken Wire Bins';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = '5 gallon buckets')
where item_name = '5 gallon buckets';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Spray Paint')
where item_name = 'Spray Paint';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Open Sign')
where item_name = 'Open Sign';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Pricing Sign')
where item_name = 'Pricing Sign';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Calrecycle Sign')
where item_name = 'Calrecycle Sign';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Water Dispenser')
where item_name = 'Water Dispenser';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Water Bottles')
where item_name = 'Water Bottles';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Safe')
where item_name = 'Safe';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Security System')
where item_name = 'Security System';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Hand Sanitizer')
where item_name = 'Hand Sanitizer';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Working Gloves')
where item_name = 'Working Gloves';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Cleaning Gloves')
where item_name = 'Cleaning Gloves';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Ear Plugs')
where item_name = 'Ear Plugs';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Safety Goggles')
where item_name = 'Safety Goggles';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Office Desk')
where item_name = 'Office Desk';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Change/Cash Deposit Bags')
where item_name = 'Change/Cash Deposit Bags';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Laptop')
where item_name = 'Laptop';

update equipment_needed
set  `catalog_id` = (select `id` from equipment_catalog where item_name = 'Writing Pens')
where item_name = 'Writing Pens'

;

Answer 1

尝试这样的事情:

update equipment_needed as t1
set  `catalog_id` = (select `id`
    from equipment_catalog as t2
    where t2.item_name = t1.item_name)