SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Non-Member widedesigns's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql Query Help

    I have two tables

    Table A
    ************
    id (autoincrement)
    Category
    Title
    Content

    Table B
    **************
    id (autoincrement)
    Category
    Title
    Content



    Both Table A and Table B has records and the "id" field is conflicting. Means both start at 1,2,3 etc...I need to merge these table to one...That is i want to insert category,Title,Content from Table B to Table A...these new rows should have the next autoincemented id value of Table A

    can anyone help me with the query?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    INSERT INTO TableA
    SELECT 0, Category, Title, Content
    FROM TableB

  3. #3
    Non-Member widedesigns's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    INSERT INTO TableA
    SELECT 0, Category, Title, Content
    FROM TableB
    ok thanks!it worked wow!

    Now one more problem


    Table C
    *********
    catid
    catname

    Table D
    *********
    id
    catname
    content
    catid


    both table has data but catid field in table D is empty.

    I want to fill catid field in table D with the catid values in table C using the condition table C.catname= table D.catname


    Anyone?Thanks

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    May I ask you what the use of this duplication of data is? If the catname depends on the catid, then why do you want to have the catname in table D, since you already have it in table C? Isn't the link between the two tables by catid enough?

  5. #5
    Non-Member widedesigns's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    May I ask you what the use of this duplication of data is? If the catname depends on the catid, then why do you want to have the catname in table D, since you already have it in table C? Isn't the link between the two tables by catid enough?
    Yah but the table structure is predefined one of a CMS which i dont want to alter and mess up as there are so many dependencies

    Also i actually want the catid in Table D,catname is already there in Table C and Table D.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    UPDATE TableD
    SET catid =
    (SELECT catid
    FROM TableC
    WHERE TableC.catname = TableD.catname)

  7. #7
    Non-Member widedesigns's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that also worked!!! Thanks

    now one more

    i have a column with some names . but the names start with lowercase. I want to make the first letter of the names uppercase?any idea?

    eg: indiai have to make India

    Quote Originally Posted by guido2004 View Post
    UPDATE TableD
    SET catid =
    (SELECT catid
    FROM TableC
    WHERE TableC.catname = TableD.catname)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE daTable
       SET Name = CONCAT(UPPER(SUBSTRING(Name FROM 1 FOR 1))
                        ,SUBSTRING(Name FROM 2))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •