Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Location
    China
    Posts
    54

    Question Unanswered: Update query to append text from one field to another

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql does not use the plus sign for concatenation

    it seems you have some microsoft sql server bad habits to unlearn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Ahhh, thanks for this.

    I take it I should use & ?

  4. #4
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat

  6. #6
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Trumpet View Post
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Trumpet View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    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.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The syntax is
    Code:
    WHERE images IS NULL 
      AND fulltext IS NULL

  12. #12
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    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:
    WERE images IS NULL AND fulltext <> ""
    Thanks for everyone's help!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Trumpet View Post
    (I guess as fulltext was a text string or something)
    it's a keyword -- FULLTEXT is a type of index

    Code:
    `fulltext` IS NOT NULL
    or
    Code:
    `fulltext` > ''
    you need to escape it with backticks (or preferably rename it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •