Mysql – Set all images name of a product to the same field in other table separated by comma in MySQL

MySQL

I'm using MariaDB v10.1.31. I have 2 tables:

Product_images (created from a .csv import).

product_reference | name

REF.001           | example_image_1.jpg   
REF.001           | example_image_2.jpg

Importer* (created to store all the data I need from others tables).

sku     | images

REF.001 | null

How could I update Importer to show the following?

sku     | images

REF.001 | example_image_1.jpg,example_image_2.jpg

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the images by this way.

Best Answer

You can use GROUP_CONCAT().

UPDATE importer 
JOIN (
    SELECT product_reference, GROUP_CONCAT(name) AS name
    FROM Product_images 
    GROUP BY product_reference
) pi ON importer.sku= pi.product_reference
SET importer.images = pi.name;

I should mention though, that your schema design may not be the best, depending on what you want to do. Read about normal forms, if you don't know what that is. A OLTP database usually is in at least 3rd normal form. Comma separated values violate the 1st normal form already.

Related Solutions

Mysql – How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id = 
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
)

Suppose we have a CSV file with the following content

ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"

Let's try import it:

mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)

There you have it.

MySQL How to copy all data in tables from one huge database to another in the same server

You can query information schema for list of all tables in your source database and build the import commands.

SELECT
    CONCAT('INSERT INTO target_db.', TABLE_NAME, ' SELECT * FROM source_db.', TABLE_NAME, ';') restore_command
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA='source_db'
    AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Then copy/paste the output in query window.

Best Answer

Related Solutions

Mysql – How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

MySQL How to copy all data in tables from one huge database to another in the same server

Related Question