CODEWITHSUNDEEP
mysqlsql
Jason
Jason

Reputation: 29

MySQL update across tables

Guys I have a MySQL database table I'm trying to fill out with data from another table but mapped against a common code.

eg:

UPDATE signs_labels_products p 
SET p.ebay_cat_id = (
    SELECT c.ebay_cat_id
    FROM signs_labels_ebay_categories_by_signcode c
    WHERE p.SignCode = c.sign_code
)
WHERE p.ebay_cat_id = '' OR p.ebay_cat_id IS NULL LIMIT 5000;

which runs fine but slowly (hence the limit clause).

The problem I have is that I intend to fill out other fields at the same time, something like:

UPDATE signs_labels_products p 
SET p.ebay_cat_id =    (SELECT c.ebay_cat_id
                        FROM signs_labels_ebay_categories_by_signcode c
                        WHERE p.SignCode = c.sign_code),
    p.ebay_category =  (SELECT c.ebay_category
                        FROM signs_labels_ebay_categories_by_signcode c
                        WHERE p.SignCode = c.sign_code),
    p.store_category = (SELECT c.store_category
                        FROM signs_labels_ebay_categories_by_signcode c
                        WHERE p.SignCode = c.sign_code)
WHERE p.ebay_cat_id = '' OR p.ebay_cat_id IS NULL LIMIT 5000;

This is not the best and can surely be a lot tidier so thoughtI'd ask you guys for your input and help.

Any improvements greatly appreciated :)

Thanks for reading anyway,.

JB

Upvotes: 0

Views: 60

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

Why not use JOIN instead of sub-query

UPDATE signs_labels_products p 
join signs_labels_ebay_categories_by_signcode c on c.sign_code = p.SignCode
set 
p.ebay_cat_id = c.ebay_cat_id,
p.ebay_category = c.ebay_category,
p.store_category = c.store_category
WHERE p.ebay_cat_id = '' OR p.ebay_cat_id IS NULL

And make sure that the joining key are indexed and if not do as

alter table signs_labels_products add index SignCode_idx(SignCode);
alter table signs_labels_ebay_categories_by_signcode 
add index sign_code_idx(sign_code)

Upvotes: 2

Khurram Ali
Khurram Ali

Reputation: 1679

UPDATE signs_labels_products p 
inner join signs_labels_ebay_categories_by_signcode c 
on p.SignCode = c.sign_code
SET 
    p.ebay_cat_id =   c.ebay_cat_id   ,
    p.ebay_category =   c.ebay_category ,
    p.store_category = c.store_category                   
WHERE p.ebay_cat_id = '' OR p.ebay_cat_id IS NULL LIMIT 5000;

Upvotes: 0

Related Questions