SQL query help - merge a value to all rows in a column

Question

I'm trying to migrate a site from a joomla system to a drupal. The problem is that drupal needs filename and sourcepath in the same row, but joomla only has filename. I'm looking for a way to add sourcepath before the filename in all the rows in that column. I'm figuring it's the UPDATE statement that I should use, but I can't figure out how to construct the query.

There's a person with a similar problem here, but I don't find the answers in that thread helpful to my problem: http://www.daniweb.com/forums/showth...t+value&page=2

Any suggestions?

Answer 1

To append a value from one column to the start of another (assuming both columns are on the same table):

update files
set
   filename = CONCAT(path, filename)
from files

(you may need to correct the table name / column names.. i'm just guessing)

Answer 2

@Dexter, thanks for leading me to the CONCAT function! It solved the problem. First I made a new column called path and updated all rows with the path to the images. Then I ran this query: UPDATE jos_joomgallery SET imgfilename=concat(path,imgfilename);

jos_joomgallery is the table, of course. I got an error when I used the FROM command, appearantly it wasn't needed.

@tomfanning, I don't know what a string literal is, I googled a little but couldn't find any information of use. Thanks anyway.

@Jaxidan, I guess that would work, but I can't figure out how to refer the filename to the column where they are stored.