MySQL: Update Rows Based on Specific Set of Entry IDs

Question

I have a random group of about 40 entries in the same channel where I need to copy the url_title from exp_channel_titles over to the uri column in exp_structure_listings. If I wanted to copy the urls for all entries there'd be no problem, but I need to copy only the urls from this specific group of entry IDs. I tried:

UPDATE 
    exp_structure_listings s, exp_channel_titles t
SET 
    s.uri = t.url_title
WHERE 
   s.entry_id = t.entry_id
AND
   t.channel_id = '17'
AND (
      t.entry_id = '2128'
   OR t.entry_id = '2142'
   OR t.entry_id = '2171'
)

But, not surprisingly, that didn't work. I feel like I should be using a join in here somewhere but I'm not really sure what the syntax should look like.

Answer 1

You'd want to use the IN() function, so something like:

UPDATE 
    exp_structure_listings s 
INNER JOIN 
    exp_channel_titles t 
ON 
    s.entry_id = t.entry_id 
SET 
    s.uri = t.url_title 
WHERE 
    t.channel_id = '17' 
AND 
    t.entry_id 
IN (
    2128, 
    2142, 
    2171
) 

I'm not certain if that statement would work exactly for you, since it seemed as if you had a JOIN in the WHERE clause, without specifying a join. But that should provide enough to fiddle with.

IN() is best used when you have a known list of items. It's performance in subqueries can be slow, but it's very speedy with a known list. So you could do batches of 10 or the whole 40, if you liked.