Inserting a concatenated value into a table-CodePudding

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.

CodePudding user response：

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.