Select Multiple Rows and Combine / Insert into One Row

[iru76]

Hi All,

I am attempting to merge something like this in my MySQL database:

Name | Dept
=========
John Account
Wayne Account
James Management
Bond Management

into something like this:

Name | Dept
=========
John,Wayne Account
James,Bond Management

So far, I have found that by running the MySQL Script:

SELECT dept,
GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ',')
FROM Table
GROUP BY dept

will do the stuff I need in getting the data out, but how do I then run a insert / update to insert it into the column Name?

Any help is greatly appreciated. Thanks in advance.

[Atli]

Hi.

You really, really shouldn't try to do that.

You should never put more than one piece of data into a single field. It is a very bad idea, because in that format, your database has no control over it.
MySQL will consider it all to be a single string, which puts your data at a much greater risk of getting corrupted, and makes it that much more complex for the front-end software to actually use the data.

I suggest you check out this article. It explains the 3 basic rules you should always keep in mind when designing a database.
http://bytes.com/topic/access/insigh...ble-structures.

[iru76]

Hi Atli,

Thanks for the reply. I do understand the basic principle behind database design, however, in this case, the software I am using required that the data be combined into a single row in a single column for it to function, thus the question on how to do it... :)

I've so far been able to work out how to use GROUP_CONCAT, but unfortunately, how to tie in updating the table's column and row with the new data is still beyond me.

If i was to use a temp table, will it help? eg:

Table1
NAME | DEPT
==========
JOHN ACCOUNT
DOE ACCOUNT
JANE MANAGEMENT
DOE MANAGEMENT

with the new data combined into a new table like:

Table2
NAME2 | DEPT2
===========
JOHN,DOE ACCOUNT
JANE,DOE MANAGEMENT

[r035198x]

Introducing the other table will give rise to the need to synchronize it's contents with the original tables. If your application requires the fields to be concatenated for view purposes only then there is no need to change the tables. For the update part, your front end language should catch the data and convert it into the format required by the back end.
Basically database design should be done independently of how the data needs to be presented. In this case I think that you are trying to change the back end to satisfy a presentation format which should never be done.

[iru76]

Hi,

Perhaps a more detail description of the issue i'm facing will help everyone understand why I'm trying to do it. :)

I have a Postfix server which is dependent on the MySQL database backend. However, to create a mass mailing list that is auto updated, I am trying to write a sql script to grip the data from the the mailbox table and update the alias table sorted by domain.

As i have tried to do so from the applications and it has never worked (postfixadmin) and found out that the previous sysadmin also use the method of copy and paste all the address into the rows to update it.

So in brief, I'm trying to grip from :

Mailbox
username | domain
==============
[e-mail] abc.com
[e-mail] abc.com

and combine the data into a single row in the alias table:

Alias
goto | domain
==========
[e-mail],[e-mail] abc.com

So I truely hope you guys can help me out on this, as I'm at a lost on how to go about it. :(

[Atli]

You could try using a view. That way you wouldn't have to mutilate the actual data but you could still convert it into whatever format your software needs.

So, using the query you posted in the first post:

CREATE VIEW `nameListView` AS
SELECT dept,
GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ',')
FROM Table
GROUP BY dept
 

You should be able to treat `nameListView` as an actual table, which would return the data precisely the way your wanted it.

[iru76]

So in this case, if i use the example for my DB:

CREATE VIEW `nameListView` AS 
SELECT domain, 
GROUP_CONCAT(username ORDER BY username ASC SEPARATOR ',') 
FROM mailbox 
GROUP BY domain

followed by:

UPDATE alias
SET goto = nameListView

Will that update the goto column in the alias table with the data?

[Atli]

No. The `nameListView` VIEW acts as a regular table.
Meaning you don't actually have to pull the data into a temporary table. Just querying the VIEW will work.

So you can do:

SELECT * FROM nameListView

And get the results of the query you created the VIEW with, just like if you had create a table using the data from the query.

So if you need a table named `alias` that should be structured like the return data of a query, create a VIEW named `alias` instead using the query.
Then simply have your software query the `alias` VIEW.

You can even set the "field" names of the *fake* table your VIEW represents by using the AS clause in the query that creates it.
For example, if you have this view:

CREATE VIEW `TimeAndDate` AS
SELECT
  DATE(NOW()) AS 'CurrentDate',
  TIME(NOW()) AS 'CurrentTime';
 

And you do:

SELECT * FROM `TimeAndDate`

You would get:

+-------------+-------------+
| CurrentDate | CurrentTime |
+-------------+-------------+
| 2009-01-08  | 19:51:42    |
+-------------+-------------+

[iru76]

Ah ok. Understand.

If possible, I belief I will need the data to be written to an actual table call 'alias' into an actual row 'goto', as the program (postfix) are relatively hard coded and I don't have the ability to change the codes in it.

I do know that the general practice is to use view for select and display, but in this case, as the table is also used by other parts of the program which require the same format of data (ie. concated longtext), I belief it would be easier if it was done this way, as the alternative will be to rewrite the whole postfix mail program.

So basically, I need to grip the 'username' from the table 'mailbox' grouped by 'domain' and format it into a long string separated by ',' and insert the whole string into a field call 'goto' in the 'alias' table which has the same domain as mailbox's domain.

Hope you guys can help me with this... :)

[Atli]

OK. Personally, I would use a View to do this. Your software shouldn't be able to tell the difference between an actual table or a VIEW.

But, if you want a table, that is possible to.
You can either create the table using a SELECT statement:

CREATE TABLE `alias` AS
SELECT
    `domain`,
    GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
FROM `mailbox`
GROUP BY `domain`;

Note, the fields in the new table would be named after the rows the SELECT returns, which is why I added the AS keyword to the GROUP_CONCAT

Or create the table first, and insert the data into it:

INSERT INTO `alias`(`domain`, `goto`)
SELECT
    `domain`,
    GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
FROM `mailbox`
GROUP BY `domain`;
 

Note that the names of the rows returned by the SELECT must be the same as the names of the fields, or you will get an incorrect column count error

But...
This VIEW should be able to replace the tables created by both of those methods:

CREATE VIEW `alias` AS 
SELECT
    `domain`,
    GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto'
FROM `mailbox`
GROUP BY `domain`;

I recommend you try that first, just to see if it will work :)

[iru76]

Thanks!

Ok. I have tried the SQL command for insert and it sort of work for me. But some fields already have values in it and probably a UPDATE will work better for me in those cases I think.

So if i were to use the UPDATE command, would it look something like this?

UPDATE alias
SET goto = (
SELECT 
    `domain`, 
    GROUP_CONCAT(`username` ORDER BY `username` DESC SEPARATOR ',') AS 'goto' 
FROM `mailbox` 
GROUP BY `domain`
)
WHERE alias.domain = mailbox.domain;

[Atli]

The subquery would have to be changed.
A sub-query like that can only return 1 column from 1 row, so you wold have to limit it to that.

A correlated subquery with the GROUP_CONCAT function should do the trick.

For example:

UPDATE newTable AS nt
SET value = (
  SELECT GROUP_CONCAT(value SEPARATOR ',')
  FROM oldTable AS ot
  WHERE nt.group = ot.group
  GROUP BY ot.group
);

Note, however, that this would only update groups that already exists in the "newTable". Those that do not would NOT be inserted.

P.S.
Please use [code] tags when posting your code examples.
Thanks.

[iru76]

Hi,

I've tested the script, need a bit of twigging but it is working as intended... but... I hit another snag... when updating the alias table, the group_concat cut off after a certain number of char... which to my dismay, not what i wanted...

So on a related question, is there anyway to increase the group_concat cache or another way to grip and update the field? (I think i got around 10k emails there)

:)

[Atli]

What is the type of the target field?
Could it be that the field is to small to contain all the data?
A VarChar(255) would cut of anything beyond the 255 chars.
And a Text field would only hold up to 65.536 bytes of data.

[iru76]

Hi,

Currently using LongText. I think that should hold about 4GB of data right?

[Atli]

Ok. Seems the result of the GROUP_CONCAT is limited by a couple of variables.

From the manual

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

So, to increase this you would have to add/edit these to your my.cnf/my.ini file:

# Would set the max length to 2MB
group_concat_max_len = 2097152
max_allowed_packet = 2097152