Update query to append text from one field to another

Trumpet · #1 (**permalink**) 02-26-11, 00:09

Hi,

I have two columns/fields:
1. fulltext [mediumtext]
2. images [text]

I need to append the contents of the images column to the fulltext column and add some other standard text as well. At this stage I have:

Code:

UPDATE jos_content 
SET `fulltext` = '<img src="/images/stories/' + images + '"/>' + `fulltext`

It seems to run, but it basically resulted in deleting all of my fulltext column entries.

I was going to add this to focus just on the images entries with content:

Code:

WHERE images NOT NULL

...but this won't solve the deletion problem.

I guess the field types could be a problem, and the double parenthisis "" could be the other... any help is very much appreciated.

r937 · #2 (**permalink**) 02-26-11, 02:05

mysql does not use the plus sign for concatenation

it seems you have some microsoft sql server bad habits to unlearn

Trumpet · #3 (**permalink**) 02-26-11, 03:05

Ahhh, thanks for this.

I take it I should use & ?

Trumpet · #4 (**permalink**) 02-26-11, 04:44

I have changed the + to & but still get an error. I have also tried removing all the " , / , and > from the text I wanted to append but still with errors....

Any more specific help possible? thanks in advance.

shammat · #5 (**permalink**) 02-26-11, 05:01

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat

Trumpet · #6 (**permalink**) 02-26-11, 06:58

Great... getting there, but unfortunately the follows still throws an error in the SQL:

Code:

UPDATE jos_content 
SET `fulltext` = CONCAT('<img src="/images/stories/',CAST(images AS MEDIUMTEXT),'"/>',`fulltext`) 
WHERE images NOT NULL

r937 · #7 (**permalink**) 02-26-11, 07:12

Quote:

Originally Posted by Trumpet

Great... getting there, but unfortunately the follows still throws an error in the SQL

my copy of Microsoft® CrystalBall© is down at the moment, and i can't see the error message from here, any chance you can post it?

i mean, i can guess, but i'm trying to make a point to you about how to ask for help in forums...

Trumpet · #8 (**permalink**) 02-26-11, 07:19

Sorry. I am using Navicat for the first time and until you asked I didn't realise the error said more than just "error: you have an error in your SQL". However, clicking on this I realised the full error is:

error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MEDIUMTEXT),'"/>',`fulltext`)
WHERE images, `fulltext` NOT NULL' at line 2

Sorry for the omission. Definately appreciate your help.

r937 · #9 (**permalink**) 02-26-11, 08:29

Quote:

Originally Posted by Trumpet

Sorry for the omission. Definately appreciate your help.

no problem, it shows you are willing to learn

the error message says the query died on MEDIUMTEXT inside the CAST function

go to da manual and look up the CAST function, and check out what the allowable options are

Trumpet · #10 (**permalink**) 02-26-11, 09:59

Fantastic! Thanks for your patience r937, I have figured the mysteries of the manual!

I have got this to run to run:

Code:

UPDATE jos_content 
SET `fulltext` = CONCAT('<img src="/images/stories/',CAST(images AS CHAR),'"/>',`fulltext`) 
WHERE images IS NOT NULL

It updated the 500+ rows with images for me. Fantastic.

I wasn't able to get the "WHERE images AND `fulltext` IS NOT NULL" to work however as zero rows were updated for some reason. Have read several pages in the manual on the use of NULL and IS NOT NULL but can't find the issue with the two.

shammat · #11 (**permalink**) 02-26-11, 10:11

The syntax is

Code:

WHERE images IS NULL 
  AND fulltext IS NULL

Trumpet · #12 (**permalink**) 02-26-11, 11:23

Thanks, I got it sorted. I had tried this syntax but was still getting a strange outcome... turns out (I guess as fulltext was a text string or something) I needed to say:

Code: