CODEWITHSUNDEEP
mysqldatabase
Max Uland
Max Uland

Reputation: 87

How to reduce the query count

I am trying to produce a catalog table, so that every time I add to 2 other tables it will be the same id. Currently, I have to run the multiple query to produce. Just want to try and simplify it. This is my raw query any consolidation ideas are appreciated!

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'

;

Upvotes: 0

Views: 96

Answers (3)

Max Uland
Max Uland

Reputation: 87

update equipment_needed as t1 set item_name = (select item_name From equipment_catalog as t2 WHERE t2.id = t1.needed_id) ; update equipment_needed as t1 set quantity_type = (select quantity_type From equipment_catalog as t2 WHERE t2.id = t1.needed_id) ; update equipment_needed as t1 set category = (select category from equipment_catalog as t2 where t2.id = t1.needed_id) ;#run for equipment_needed

update equipment_owned as t1 set item_name = (select item_name From equipment_catalog as t2 WHERE t2.id = t1.owned_id) ; update equipment_owned as t1 set quantity_type = (select quantity_type From equipment_catalog as t2 WHERE t2.id = t1.owned_id) ; update equipment_owned as t1 set category = (select category from equipment_catalog as t2 where t2.id = t1.owned_id) ;#run for equipment_owned

Flipped it around to use the ID for verification instead of name since its simpler.

Upvotes: 0

James Z
James Z

Reputation: 12317

This doesn't seem to be SQL Server question, but I would assume it works this way in MySQL too:

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

If you need to restrict which names are fetched, you can add further criteria in the end, like this:

...
where item_name = equipment_needed.item_name)
item_name in ('Calrecycle Sign', 'Water Dispenser', ...);

Upvotes: 1

Iłya Bursov
Iłya Bursov

Reputation: 24146

try something like this:

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

Upvotes: 3

Related Questions