How to select a value and then update a field

Question

I have two tables, one that stores the the id of the an old img

 id | old_img_name | old_img_id

and another table that stores the info of the current img

id |img_name | img_w | img_h | etc....

and im trying to create a query to update(switch) the info from the old table into the new one, the only field that I want to update is the img_name.

I have create two queries to do that :

SELECT id, old_img_id, old_img_name FROM `img_archives` WHERE id = 3 UPDATE imgs SET img_name = old_img_name WHERE id = old_img_id

I want to convine these two queries into one, but I'm having a lot or problem doing it.

I have tried this:

UPDATE imgs SET img_name = img_archives_old_img_name FROM imgs INNER JOIN img_archives ON imgs_id = img_archives.old_img_id;

not is not working

Also if you want to update the image name from one table to another why is the img_name hardcoded to test? — Brad
– Brad, Commented Feb 20, 2019 at 20:47
Because that were I'm lost, i don't know how to get the name from the other table in one query — Pedro
– Pedro, Commented Feb 20, 2019 at 20:48

halfer · Accepted Answer · 2025-05-11 11:31:17Z

UPDATE imgs i INNER JOIN img_archives ia ON i.imgs_id = ia.old_img_id SET i.img_name = ia.old_img_name;

More info is here: http://www.mysqltutorial.org/mysql-update-join/

Brad · Accepted Answer · 2019-02-20 20:50:12Z

-- this should do it UPDATE imgs SET img_name = img_archives.old_img_name FROM imgs INNER JOIN img_archives ON imgs_id = img_archives.old_img_id;

halfer · Accepted Answer · 2025-05-11 11:30:10Z

1

In MySQL the update with join should use this syntax. If you want update the id = 3

UPDATE imgs INNER JOIN img_archives ON imgs.imgs_id = img_archives.old_img_id AND img_archives.id = 3 SET img_name = "test"

edited May 11 at 11:30

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 20, 2019 at 20:50

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

Pedro Over a year ago

Im trying your solution and I keep getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM imgs INNER JOIN

Collectives™ on Stack Overflow

How to select a value and then update a field

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related