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;

You are doing it in the a single query (working statement)!

Quicksilver
– Quicksilver

2013-04-09 09:25:01 +00:00
Commented Apr 9, 2013 at 9:25 — Quicksilver
– Quicksilver, Commented Apr 9, 2013 at 9:25
This might help you stackoverflow.com/questions/4361774/…

Deepanshu Goyal
– Deepanshu Goyal

2013-04-09 09:25:51 +00:00
Commented Apr 9, 2013 at 9:25 — Deepanshu Goyal
– Deepanshu Goyal, Commented Apr 9, 2013 at 9:25
or this might also stackoverflow.com/questions/1871537/…

Deepanshu Goyal
– Deepanshu Goyal

2013-04-09 09:26:54 +00:00
Commented Apr 9, 2013 at 9:26 — Deepanshu Goyal
– Deepanshu Goyal, Commented Apr 9, 2013 at 9:26

jfxninja · Accepted Answer · 2018-05-18 10:13:28Z

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, 2018 at 10:13

jfxninja

3813 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

Isn't product.price will refer to the old value of product_shop.price?

d'alar'cop · Accepted Answer · 2013-04-09 09:24:32Z

4

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

answered Apr 9, 2013 at 9:24

d'alar'cop

2,3551 gold badge15 silver badges18 bronze badges

1 Comment

Soheil Rahsaz Over a year ago

check this link: dev.mysql.com/doc/refman/8.0/en/ansi-diff-update.html

Collectives™ on Stack Overflow

MySQL Update Multiple Fields with Same Value

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related