1

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.

1 Answer 1

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.

4
  • That looks awesome, thanks! My only question is how does it know to match the entry ids from the first table with the entry ids from the other? Is that where the JOIN comes in?
    – kmgdev
    May 1, 2013 at 15:04
  • The full query (w/ the JOIN) would be -- 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 AND t.entry_id IN (2128, 2142, 2171) May 1, 2013 at 17:31
  • Yes, that's where the JOIN would be required, but I wasn't certain from your first query if you were updating both data tables, or just one. If just one, then Dave's addition should work, without the first t.entry_id AND. May 1, 2013 at 17:40
  • Works a treat, thanks guys! I edited NPT's answer (needs approval) to include the JOIN & accepted.
    – kmgdev
    May 3, 2013 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.