I use Drupal to build development versions on my server
and show them to clients. When the client ponies up the money
I move the site out to it's own live server.

I have found that there are occasionally issues with images
where the path to the file doesnt get translated properly.

For example, if the path on my dev site is
www.mydev.com/client1/sites/all/themes/nicetheme/images/imagename.png

I want the path to be

www.clientlive.com/sites/all/themes/nicetheme/images/imagename.png

Trouble is that it sometimes picks up the first level directory so the url
trying to find the image borks out like this:

www.clientlive.com/client1/sites/all/themes/nicetheme/images/imagename.png



I figure that there must be an SQL query that I could run through myPHPadmin that would strip out the unwanted subdirectory from any links which contain it.

But being a bit of an SQL noob I defer to the powers that be here.
Any advice?

Is this any use?
http://drupal.org/node/199484

Possibly. But if I am reading the thread correctly, the final advice is to use a preprocessor function in the themes layer.
It would probably work but I'd like to fix the problem upstream.
There is also the potential problem that if somebody switches presentation themes later on, everything breaks.

Maybe I am smoking crack here but would an SQL query like this, run through the phpMyAdmin tab work?

UPDATE * SET * = replace(*, 'clientlive.com/client1/', 'clientlive.com/
') WHERE * = 'clientlive.com/client1/';
';

Where * is a global wildcard.

A query like that would definitely not work.

UPDATE queries require a table name not a wildcard, so to update multiple rows in multiple tables based on a criteria would need to be done with a stored procedure, and to be honest, writing and testing a recursive procedure that was testing against every column, in every row, in every table in a database would not be my first choice of a fun thing to do.

Even if I was spending a wet weekend in Rhyl

I've been to Wales. Thought it was quite nice actually.

Guess I need to brush up on my SQL.

OK maybe this will make a bit more sense.
So far as I can tell there is only one table in Drupal that
contains data on links to uploaded images and files.
That is the "drp_node_revisions" table.

And the only fields that need updating are the "body" field and the "teaser" field.

So the following query will successfully select all the required
records:

SELECT * FROM drp_node_revisions WHERE body LIKE '%/sites/default/files/%' 
OR teaser LIKE '%/sites/default/files/%';

From that point, it is possible to go through each record and manually change the file reference.

However that's only a slight improvement on manually updating everything
from the Admin interface. I want to take it to the next level and run an SQL query that does that.

Would this work?:

UPDATE drp_node_revisions SET body, teaser
 = replace(*, '/client1/sites/default/files/', '/sites/default/files/')
 WHERE body LIKE '%/sites/default/files/%' 
OR teaser LIKE '%/sites/default/files/%';

I'm not sure if a % sign in the replace parameters is necessary or not, because it is affecting a part of a longer string,
but the MySQL docs suggest not.

http://dev.mysql.com/doc/refman/5.0/...functions.html