Update Table to last ID from another table

Question

I want to run something like

 UPDATE TABLE profile
 SET profile_photo = (SELECT photo_id FROM photos WHERE profile_id 
 = 'someprofileid' ORDER BY photo_id DESC LIMIT 1;)
 WHERE 'somecolumn' = 'some criteria'

I have seen Advanced MySql Query: Update table with info from another table

But I only want the last entry which satisfies the WHERE clause, therefore the DESC and LIMIT 1. How do I included these criteria in the SET information?

(My goal is to update change a profile picture to the most recent profile picture, after e.g a delete or sth)

remove ; after limit 1 – Piotr Rogowski Mar 08 '17 at 12:12 — Piotr Rogowski, Mar 08 '17 at 12:12

score 1 · Accepted Answer · answered Mar 08 '17 at 12:10

1

use select query with limit and desc and get id and fire update on that id simple

answered Mar 08 '17 at 12:10

Nishit Manjarawala

451
4
20

score 1 · Answer 2 · answered Mar 08 '17 at 12:16

update supports order by and limit:

 UPDATE TABLE profile p
     SET profile_photo = (SELECT photo_id
                          FROM photos ph
                          WHERE ph.profile_id = 'someprofileid'
                          ORDER BY photo_id DESC
                          LIMIT 1
                         )
     WHERE p.'somecolumn' = 'some criteria'
     ORDER BY p.id DESC   -- you need to order by something
     LIMIT 1;

Update Table to last ID from another table

2 Answers2