I have 2 tables, one named pictures and another named media, in table pictures I have the field description which contains some text, In table media there is the row url that I would like to update. I need to write an sql query that updates row url in table media that uses regex for an specific string in description from table pictures, can this be done with one sql query?
-
Seeing your table structure and the relationships would help.Ilion– Ilion2013-01-18 06:12:23 +00:00Commented Jan 18, 2013 at 6:12
Add a comment
|
1 Answer
You can use REGEXP to search for strings but you cannot return the result. If that's alright then you could try something like this:
If your table is something like:
create table media(media_id int, url varchar(200));
create table pictures(pic_id int, media_id int, description text);
You could do something like this:
UPDATE pictures p
LEFT JOIN media m
ON p.media_id = m.media_id
SET url='http://newurl.com/pic.jpg'
WHERE REGEXP '.*regexpString$';
If you need to just replace part of the URL you could try:
UPDATE pictures p
LEFT JOIN media m
ON p.media_id = m.media_id
SET url=REPLACE(url, 'olddomain.com', 'newdomain.com')
WHERE REGEXP '.*regexpString$';
3 Comments
user1989379
@llion hi thanks for the response!, I need to return the response because in the field description contains part of the path that I need for updating url,
Manticore
in the first code-part you wrote picture (singular) and then you wrote pictures (plural). was this on purpose?
Ilion
Nope, that was a typo. Fixing it now.