Inserting empty column value throws error

aigini82 · August 15, 2020, 1:25pm

Hi,

I need to insert information from one table into another table. Both tables are identical in structure, indexes, etc. However, in the destination table (lz_chat_archive_dup2), there is one primary key column where the values have to be different.

Because of this, I have emptied all values in the source table (lz_chat_archive_dup1) for this column, and inserted all values from this modified table into a csv file. I also modified that column in the destination table to be auto_increment.

These are the steps to modify both tables :

CREATE TABLE lz_chat_archive_dup2 LIKE lz_chat_archive;
ALTER TABLE lz_chat_archive_dup2 MODIFY chat_id int UNSIGNED AUTO_INCREMENT;
ALTER TABLE lz_chat_archive_dup2 AUTO_INCREMENT=383933;

LOAD DATA INFILE ‘/usr/local/LivezillaDB/mysql/lz_chat_archivedup1.csv’ INTO TABLE lz_chat_archive_dup2 FIELDS TERMINATED BY ‘,’;

However, I am not sure if what I am doing is correct, as when I run the LOAD statement to insert data into the 2nd table, I get error :

mysql> LOAD DATA INFILE ‘/usr/local/LivezillaDB/mysql/lz_chat_archivedup1.csv’ INTO TABLE lz_chat_archive_dup2 FIELDS TERMINATED BY ‘,’;
ERROR 1366 (HY000): Incorrect integer value: ‘’ for column ‘chat_id’ at row 1
mysql>

Do I have to insert the first value into the 2nd table manually before able to auto-increment the rest of the values with the csv file? Else what is the correct solution for this error?

All I need is to populate the new table with new information in the primay key column chat_id, starting with the value 383933. Please help, as I am not able to get this information in the internet.

r937 · August 15, 2020, 2:53pm

one table, another table, new table, csv…

i’m confoosled

but i see your error message says you attempted to insert a zero-length string into a numeric column

here’s the best strategy

target table –

CREATE TABLE target
( autoinc  INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, column1  INTEGER
, column2  VARCHAR(23)
, column3  VARCHAR(45)
)

now what you want to do is load your csv data into this table –

source table –

CREATE TABLE source
( column1  INTEGER
, column2  VARCHAR(23)
, column3  VARCHAR(45)
)

and now you can populate your target table, with the auto_increments being assigned automatically, like this –

INSERT 
  INTO target
     ( column1 
     , column2  
     , column3 )
SELECT column1 
     , column2  
     , column3
  FROM source

aigini82 · August 16, 2020, 5:23am

Thank you for your response.
However the source and target tables have too many fields (columns).
These are all of the fields in these tables :

In this case, how do I use the INSERT INTO - SELECT statement to populate the target table with auto-increments functioning?

aigini82 · August 16, 2020, 7:38am

I have an alternative solution for this. Instead of insert-into/select from, I thought I could use just a simple code to increment only the values in the “chat_id” column.

For the table lz_chat_archive_dup1, the column “chat_id” is has empty strings (no values). This is the partial excerpt of the table :

mysql> select chat_id, fullname from lz_chat_archive_dup1 LIMIT 5;
+---------+--------------+
| chat_id | fullname     |
+---------+--------------+
|         | Yw           |
|         | Shah         |
|         | Sunny Duhel  |
|         | Leong Zi Yin |
|         | Mohd Nasir   |
+---------+--------------+
5 rows in set (0.00 sec)

I tried to insert a value for the name “Yw” like this and it worked :

mysql> UPDATE lz_chat_archive_dup1 SET chat_id = '383933' where fullname = 'Yw';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So now the table is like this :

mysql> select chat_id, fullname from lz_chat_archive_dup1 LIMIT 5;
+---------+--------------+
| chat_id | fullname     |
+---------+--------------+
| 383933  | Yw           |
|         | Shah         |
|         | Sunny Duhel  |
|         | Leong Zi Yin |
|         | Mohd Nasir   |
+---------+--------------+
5 rows in set (0.00 sec)

However, the number of rows in this table is 2589, and for me to do it one by one is tedious and time consuming :

mysql> select count(*) from lz_chat_archive_dup1;
+----------+
| count(*) |
+----------+
|     2589 |
+----------+
1 row in set (0.00 sec)

I thought I could use a code something like this to update/increment only that one column, but I don’t think this is the correct syntax for MySQL. Can you please help to correct the code to customize it to work in my situation :

DECLARE @counter int
SET @counter = 383933
UPDATE #lz_chat_archive_dup1
SET @counter = counter = @counter + 1

So with this code, what I am trying to achieve is increment the chat_id column so that the next value is always 1 integer higher than the previous one. So the first row is 383933, the next one should be 383934, 383935, 383936,…etc etc.

r937 · August 16, 2020, 9:41am

sorry, you lost me right here

what is too many?

also, are you saying your two tables are ~identical~ in structure?

also, why is chat_id a varchar(64) column? that’s not going to auto_increment no matter how hard you try

aigini82 · August 16, 2020, 9:46am

I have changed the structure of that column to INT UNSIGNED AUTO_INCREMENT now.
What about the 2nd alternative - using the code to increment the values. Can you suggest on that instead?

r937 · August 16, 2020, 9:54am

sorry, no

code is a poor solution when SQL can do the job simply

if you’ve changed the column to AUTO_INCREMENT, what exactly is not working when you employ the strategy i suggested in post #2?

aigini82 · August 16, 2020, 10:31am

I’ve changed it for the destination.
The source still remains varchar.
And for the source, the values in the column “chat_id” are empty.
So, when I try to modify it like this, I get an error :

mysql> ALTER TABLE `lz_chat_archive_dup1` MODIFY `chat_id` int UNSIGNED;
ERROR 1366 (HY000): Incorrect integer value: '' for column 'chat_id' at row 2
mysql>

Also both these tables are not production tables, so even if I use code to increment the values like I suggested earlier, it won’t affect anything. I can use sql select statements to load the data into a .csv file once the table has been populated with the incremented values using the code.
Then this .csv file will be used to push the data into the actual production table.

aigini82 · August 16, 2020, 11:35am

It works now, your suggestion, however I had to change all the values for the field chat_id in the source to 0, Then only I could change the field type to INT UNSIGNED. Then could successfully use the INSERT INTO/SELECT statement that you suggested.

r937 · August 16, 2020, 12:45pm

i should have made it more clear – you should not mention chat_id in the INSERT

please see how the “autoinc” column is treated in post #2

you need not have done anything to chat_id in your source table

aigini82 · August 16, 2020, 12:53pm

Thx, will keep take note of this.

system · November 15, 2020, 7:53pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Creating an auto-increment column Databases	3	1084	November 14, 2020
Column 'id' cannot be null Databases	15	45424	September 24, 2014
Importing csv through phpmyadmin, how to autoincrement? Databases	3	16741	September 24, 2014
Asking me to specify autoincrement value when inserting Databases	7	383	June 25, 2024
mySQL Load data loads everything but 1 column Databases	7	4121	September 24, 2014

Inserting empty column value throws error

Related topics