CODEWITHSUNDEEP
mysqlsql
user3036527
user3036527

Reputation: 75

INSERT INTO WITH SELECT resulting in multiple rows inserted instead of just one

INSERT INTO `mailinglist_archives`
              (`recipients`) 
               SELECT `email`
               FROM mailinglist

SQL RESULT:

row 1 - [email protected]

row 2 - [email protected]

row 3 - [email protected]

row 4 - [email protected]

What i need -

row 1 - [email protected],[email protected],[email protected],[email protected]

Upvotes: 2

Views: 76

Answers (2)

elixenide
elixenide

Reputation: 44831

You can do this with GROUP_CONCAT:

INSERT INTO `mailinglist_archives`
          (`recipients`) 
           SELECT GROUP_CONCAT(`email`)
           FROM mailinglist

That said, this is not a good idea; you should normalize your database, rather than storing lists in columns.

Edit to address comment:

You can combine these two queries:

$query0 = "INSERT INTO mailinglist_archives (subject, message, datesent) VALUES ('$subject', '$ckcontent', '$timestamp')";
$query01 = "INSERT INTO mailinglist_archives (recipients) SELECT GROUP_CONCAT(email) FROM mailinglist";

like this:

INSERT INTO mailinglist_archives (subject, message, datesent, recipients)
SELECT '$subject', '$ckcontent', '$timestamp', GROUP_CONCAT(email) FROM mailinglist

Again, however, this will result in a (potentially very large) list in the recipients column. This will cause problems, sooner or later, so you are better off normalizing the data by creating a new table to match recipient ids and message ids.

Upvotes: 4

Sanal K
Sanal K

Reputation: 733

Try like this

INSERT INTO `mailinglist_archives`
              (`recipients`) 
               SELECT GROUP_CONCAT(`email`)
               FROM mailinglist

Upvotes: 4

Related Questions