Inserting a concatenated value into a table

Question

With MYSQL, I can calculate the time difference and get it as a concatenated output. I am using queries sourced from here.

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),
 
differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)
 
SELECT
id,
departure,
arrival,
CONCAT(
  FLOOR(seconds / 3600 / 24), ' days ',
  FLOOR(hours_part / 3600), ' hours ',
  FLOOR(minutes_part / 60), ' minutes ',
  seconds_part, ' seconds'
) AS difference
FROM differences;

My output is something like this:

id  departure           arrival             difference
1   2018-03-25 12:00:00 2018-04-05 07:30:00 10 days 19 hours 30 minutes 0 seconds

What I would like to do is to get hold of the concatenated string of "difference" and update/insert into another table. In the other table, I have a column called "time_difference" so I have tried to use the update query. I also have a condition set where it should update only to the relevant user profile.

    UPDATE airport_database.user
    SET time_difference = (SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences)
    WHERE name = "Sir";

However, I am getting

Error #1241 - Operand should contain 1 column(s).

Therefore I also tried to get the concatenated value through:

UPDATE airport_database.user
        SET time_difference = (difference)
        WHERE name = "Sir";

And I get

Error #1054 - Unknown column of 'difference' in 'Field List'.

I would like to know why my concatenated string column is unable to be detected by MYSQL.

Any help would be greatly appreciated, thank you.

user_10138734 · Answer

DEMO.

CREATE TABLE src (id INT, departure DATETIME, arrival DATETIME);
INSERT INTO src VALUES (1, '2018-03-25 12:00:00', '2018-04-05 07:30:00');
CREATE TABLE dst (id INT, departure DATETIME, arrival DATETIME, verbal_diff VARCHAR(255));
INSERT INTO dst SELECT *, NULL FROM src;
SELECT * FROM dst;

id	departure	arrival	verbal_diff
1	2018-03-25 12:00:00	2018-04-05 07:30:00	null

UPDATE dst
JOIN (SELECT id,
             CONCAT(TIMESTAMPDIFF(DAY, departure, arrival),
                    DATE_FORMAT(TIMEDIFF(arrival, departure), ' days %H hours %i minutes %s seconds')) difference
      FROM src) data USING (id)
SET dst.verbal_diff = data.difference;

SELECT * FROM dst;

id	departure	arrival	verbal_diff
1	2018-03-25 12:00:00	2018-04-05 07:30:00	10 days 19 hours 30 minutes 00 seconds

db<>fiddle here

See UPDATE Statement, Multiple-table syntax.

Inserting a concatenated value into a table

1 Answers