mysql INSERT INTO results in NULL values

Question

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?

nbk · Accepted Answer · 2022-01-26 22:32:51Z

2

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

answered Jan 26, 2022 at 22:32

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql INSERT INTO results in NULL values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related