MySQL Update Multiple Fields with Same Value

Question

I have two fields in two seperate tables that need to be updated to the same value. Without procedures, etc. Is this possible in a single query?

Working statement:

UPDATE product,product_shop SET
 product_shop.price='737.96',
 product.price='737.96',
 product_shop.wholesale_price='479.67',
 product.wholesale_price='479.67'
WHERE 
 product_shop.id_product=product.id_product AND
 product_shop.id_product=14;

What I was hoping for:

UPDATE product,product_shop SET
 product_shop.price=product.price='737.96',
 product_shop.wholesale_price=product.wholesale_price='479.67'
WHERE 
 product_shop.id_product=product.id_product AND
 product_shop.id_product=14;

This might help you http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query — Deepanshu Goyal, Apr 09 '13 at 09:25
or this might also http://stackoverflow.com/questions/1871537/how-to-update-two-tables-in-one-statement — Deepanshu Goyal, Apr 09 '13 at 09:26

score 7 · Answer 1 · answered May 18 '18 at 10:13

7

MySQL docs state you can do this, if you are trying to avoid printing the value twice you could do the following:

UPDATE product,product_shop SET
 product_shop.price='737.96',
 product.price=product_shop.price, 
 product_shop.wholesale_price='479.67',
 product.wholesale_price=product_shop.wholesale_price
WHERE 
 product_shop.id_product=product.id_product AND
 product_shop.id_product=14;

answered May 18 '18 at 10:13

jfxninja

371
3
11

Isn't `product.price` will refer to the old value of `product_shop.price`? – Sam Nov 16 '22 at 22:46

score 3 · Accepted Answer · answered Apr 09 '13 at 09:24

3

No. Your "working query" is the best you can do.

answered Apr 09 '13 at 09:24

d'alar'cop

2,357
1
14
18

check this link: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-update.html – Soheil Rahsaz Sep 23 '20 at 12:06

MySQL Update Multiple Fields with Same Value

2 Answers2