SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: SQL question

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    IL
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL question

    Hi,

    I want to update the values of one field of a table ("upload image") using a value from another table ("category").

    The relevant tables and fields for my question (i think):

    upload_image
    category_id
    short_desc

    category
    category_id
    category_name

    I'd like to update the values of "short_desc" (table: upload image) using the values from "category_name" (table: category).

    For example the short_desc will be: "fixed text" + category_name + "another fixed text" + category_name

    What is the update SQL command i should run ? (i'm using phpMyAdmin)

    Thanks,
    E-A.

  2. #2
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the CONCAT function to do that. To give a basic example, I commonly use concat to generate full names if first and last name are separated out:

    Code:
    CONCAT(first_name, ' ', last_name) as full_name
    You can have a variable number of options, so long as they're separated by commas.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    IL
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I don't know this command and i'm pretty SQL noob...

    I also don't need a new field.. Just need to insert the category name to the short desc.

    What i know to do is:

    Code:
    update upload_image
    set `short_desc` = 'text'
    What i want to do is instead of having just a simple same text for all catogories, to be able to use the category name inside the new "short_desc" value.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    update upload_image u
      join category c
        on u.category_id = c.category_id
       set u.short_desc = c.category_name

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    IL
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot I will try this.

    How do i use both c.category_name and some other text ?

    Something like: c.category_name + 'blabla' + c.category_name + 'blublu'

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    just like chris_fuel showed you above.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    IL
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. So is it:

    Code:
    update upload_image u
      join category c
        on u.category_id = c.category_id
       set u.short_desc = c.category_name, 'blueblue', c.category_name, 'blabla'
    Or

    Code:
    CONCAT(c.category_name, 'blueblue', c.category_name, 'blabla') as full_text
    
    update upload_image u
      join category c
        on u.category_id = c.category_id
       set u.short_desc = full_text
    Or

    Code:
    update upload_image u
      join category c
        on u.category_id = c.category_id
       set u.short_desc = CONCAT(c.category_name, 'blueblue', c.category_name, 'blabla')
    Sorry for the many questions without trying first, but it's a big DB on a site that is already running and i don't want to ruin anything...

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    then definitely don't do it. you need to make a copy of the data and try it out so you're comfortable in making the changes.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    IL
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out Thanks all !

    E-A.


Bookmarks

Posting Permissions

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