So I am trying to insert the results of a query into the column 'unique_id' in my Listings_20220116 table, and all I get as a result are null values in the column. The query does not report any errors and seems to be working, but the desired values are not added to the column.

ALTER TABLE Listings_20220116 
ADD COLUMN unique_id VARCHAR(100);

INSERT INTO Listings_20220116 (unique_id)
SELECT 
  CONCAT(CAST(Date AS CHAR), Lookup)
FROM Listings_20220116;

If I create a new table using the below query then it works, but I want to add the values to the existing table:

CREATE TABLE xxx
SELECT *, CONCAT(CAST(Date AS CHAR), Lookup) AS 'Unique_ID'
FROM Listings_20220116 l ;

Any idea what I am doing wrong?

CodePudding user response：

You need an UPDATE not an INSERT

INSERT add always a new row.

CREATE TABLE Listings_20220116 (id int,`Date`DATE, Lookup varchar(5));

INSERT INTO Listings_20220116 VALUES (1, NOW(),"a"),(2, NOW(),'b'),(3, NOW(),'C');
ALTER TABLE Listings_20220116 
ADD COLUMN unique_id VARCHAR(100);

UPDATE Listings_20220116 
SET unique_id = CONCAT(CAST(Date AS CHAR), Lookup)

SELECT * FROM Listings_20220116

id | Date       | Lookup | unique_id  
-: | :--------- | :----- | :----------
 1 | 2022-01-26 | a      | 2022-01-26a
 2 | 2022-01-26 | b      | 2022-01-26b
 3 | 2022-01-26 | C      | 2022-01-26C

db<>fiddle here