MySQL, adding a new column to an already existing table, involves joins also

Question

Q. Create a new column DaysTakenForDelivery that contains the date difference between Order_Date and Ship_Date.

Tables available are: orders and shipping

CREATE TABLE orders (
    Order_ID int DEFAULT NULL,
    Order_Date text,
    Order_Priority text,
    Ord_id text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE shipping (
    Order_ID int DEFAULT NULL,
    Ship_Mode text,
    Ship_Date text,
    Ship_id text,
    DaysTakenForDelivery` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Row counts:

• shipping: 7701
• orders: 5506

Please note I changed the datatyes for date columns properly.

Things I did:

I added the required column in the table 'shipping', since nothing was specified with respect to that (which table it should be added to or otherwise). Query for the same:

ALTER TABLE shipping ADD DaysTakenForDelivery INT;

Next, I tried to update the column using various queries but nothing worked. A few of them are listed below:

FAILED ATTEMPTS:

UPDATE shipping SET DaysTakenForDelivery = (
    select datediff(b.ship_date, a.order_date) AS DaysTakenForDelivery 
    from orders a
    JOIN shipping b ON a.Order_ID = b.Order_ID
);

NOTE: this query led to the following error:

Error Code: 1093. You can't specify target table 'shipping_dimen' for update in FROM clause

Next query I tried:

UPDATE shipping b SET DaysTakenForDelivery = (
    select datediff(b.ship_date, a.order_date) AS DaysTakenForDelivery 
    from orders a  
    WHERE a.Order_ID = b.Order_ID
);

NOTE: this query led to the following error:

Error Code: 1242. Subquery returns more than 1 row

How am I supposed to achieve the desired result?

Please note I am using MySQL and answers for the same RDBMS would be appreciated for better understanding.

Version I am using: 8.0.31

Answer 1

It would be better to not store the redundant data, as there is always the risk that it becomes inconsistent, and it is just unnecessary use of storage.

It is just a normal multi-table update:

UPDATE shipping s
JOIN orders o ON s.Order_ID = o.Order_ID
SET s.DaysTakenForDelivery = DATEDIFF(s.Ship_Date, o.Order_Date);

As you appear to be having performance issues while trying to update the table, you could try updating in batches:

UPDATE shipping s
JOIN (
    SELECT s.ship_id, DATEDIFF(s.ship_date, o.order_date) AS diff
    FROM orders o
    JOIN shipping s ON o.order_id = s.order_id
    WHERE s.DaysTakenForDelivery IS NULL
    ORDER BY o.order_id, s. ship_id
    LIMIT 1000 -- batch size
) sd ON s.ship_id = sd.ship_id
SET s.DaysTakenForDelivery = sd.diff;

If that still does not work you can try reducing the batch size further.

---

Now you have added the DDL for your tables, we can see where some of your issues are coming from. The lack of a keyed relationship between the two tables is an issue and the number of rows returned by the join tells us that the Order_ID is not unique in your orders table. You need to deal with the duplicate Order_IDs before you can move forward.

To find the duplicates you can use the following queries:

SELECT *
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY Order_ID) AS num
    FROM orders
) t
WHERE num > 1;

/* And assuming Ship_id is intended to be the PK (unique identifier) */
SELECT *
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY Ship_id) AS num
    FROM shipping
) t
WHERE num > 1;

After you have dealt with the duplicates, and making sure the two date fields contain valid date strings (yyyy-mm-dd), you can run something like the following to add the primary keys, the foreign key for Order_ID in shipping, and change the datatypes of the DATE columns:

ALTER TABLE orders
    MODIFY COLUMN Order_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    MODIFY COLUMN Order_Date DATE NOT NULL;

ALTER TABLE shipping
    CHANGE COLUMN Ship_id Ship_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
    MODIFY COLUMN Ship_Date DATE,
    MODIFY COLUMN Order_ID INT UNSIGNED NOT NULL,
    ADD FOREIGN KEY (Order_ID) REFERENCES orders (Order_ID);

You will still need to address the other datatype issues but without seeing sample data it is impossible to say what the other TEXT columns should be changed to.

You might want to do some reading about:

1. Normalization
   • Wikipedia
   • Old question on SO
2. Data Types
   • MySQL docs