SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Appending values in table copy

    hi there, hope in your help.

    I've this two tables in mysql database:
    Code:
    mysql> SELECT 
           Nm,
           zone,
           sID,
           ID
            FROM
    	myTable;
    
    +-------+------+-----+-----+
    | Nm    | zone | sID | ID  |
    +-------+------+-----+-----+
    | AAA   | XY5N | 10  |  1  |
    | BBB   | XY5N | 10  |  2  |
    | CCC   | XY5N | 10  |  3  |
    | DDD   | XY5N | 10  |  4  |
    | EEE   | XY5N | 10  |  5  |
    | FFF   | XY5N | 10  |  6  |
    | GGG   | XY5N | 10  |  7  |
    +-------+------+-----+-----+
    7 rows in set
    
    
    mysql> SELECT
    	Nm,
    	zone,
    	ID
    FROM
    	myTableCopy;
    +-------+------+----+
    | Nm    | zone | id |
    +-------+------+----+
    | NULL  | XY5N | 10 |
    +-------+------+----+
    1 row in set
    I need update the field Nm of table 'myTableCopy' appending all values of tabel 'myTable' where the same 'zone'.

    I tried this query, don't have error but the update not working, can you help me please?
    thank you in advance.

    the output:
    Code:
    +------------------------------------+------+----+
    | Nm                                 | zone | id |
    +------------------------------------+------+----+
    | AAA; BBB; CCC; DDD; EEE; FFF; GGG  | XY5N | 10 |
    +------------------------------------+------+----+
    the query:
    Code:
    UPDATE myTableCopy t1
    JOIN (
    	SELECT
    		Nm,
    		zone
    	FROM
    		myTable
    ) AS t2 ON t1.Zone = t2.Zone
    SET t1.Nm = CONCAT(t1.Nm, ';', t2.Nm);
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...n_group-concat

    In your case (?):
    Code:
    mysql> SELECT zone,
        ->     GROUP_CONCAT(DISTINCT zone
        ->               ORDER BY zone DESC SEPARATOR '; ')
        ->     FROM myTable
        ->     GROUP BY zone;

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    In your case (?):
    you are on the right track with GROUP_CONCAT

    however, the example you gave is wrong

    if you do GROUP BY zone, then each distinct zone will produce one row in the result

    so the GROUP_CONCAT of that zone will have just one value

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you are on the right track with GROUP_CONCAT

    however, the example you gave is wrong

    if you do GROUP BY zone, then each distinct zone will produce one row in the result

    so the GROUP_CONCAT of that zone will have just one value

    Hello Rudy!
    You have right... definitely
    Code:
    mysql> SELECT
    	zone,
    	GROUP_CONCAT(
    		DISTINCT Nm
    		ORDER BY
    			Nm ASC SEPARATOR '; '
    	) AS concat_nm
    FROM
    	`dotables`
    GROUP BY
    	zone;
    +------+-----------------------------------+
    | zone | concat_nm                         |
    +------+-----------------------------------+
    | XY5N | AAA; BBB; CCC; DDD; EEE; FFF; GGG |
    +------+-----------------------------------+
    1 row in set
    
    -- ----------------------------
    -- Table structure for `dotables`
    -- ----------------------------
    DROP TABLE IF EXISTS `dotables`;
    CREATE TABLE `dotables` (
      `Nm` varchar(255) DEFAULT NULL,
      `zone` varchar(255) DEFAULT NULL,
      `sID` int(10) DEFAULT NULL,
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of dotables
    -- ----------------------------
    INSERT INTO `dotables` VALUES ('AAA', 'XY5N', '10', '1');
    INSERT INTO `dotables` VALUES ('BBB', 'XY5N', '10', '2');
    INSERT INTO `dotables` VALUES ('CCC', 'XY5N', '10', '3');
    INSERT INTO `dotables` VALUES ('DDD', 'XY5N', '10', '4');
    INSERT INTO `dotables` VALUES ('EEE', 'XY5N', '10', '5');
    INSERT INTO `dotables` VALUES ('FFF', 'XY5N', '10', '6');
    INSERT INTO `dotables` VALUES ('GGG', 'XY5N', '10', '7');

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Wow!

    thanks to all !!!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •