0

How do I do something like :

INSERT INTO tablename SET customer_id = '' WHERE <condition>;

customer_id is BIGINT NULL Default NULL

But when I execute SET customer_id = '' it gives

Incorrect integer value: '' for column 'customer_id' at row 1

3
  • 1
    But WHY you try to insert string into numeric column?
    – Akina
    Mar 29, 2023 at 10:27
  • I wrote a migration script to load relevant fields' data data from an older database and created INSERT statements to load into the new table where instead of varchar or text is given for numbers since the new table's columns are Integer instead of varchar / text.
    – anjanesh
    Mar 29, 2023 at 15:31
  • so, "fix the migration script" is the best answer. Mar 30, 2023 at 7:41

1 Answer 1

0

Becaust is is a Integer field you cannot fill that field with a (empty) "" string value.

If you insert a (empty) record without specifying the customer_id field it will default to NULL (just do not specify it in the insert).

If you want to clear out the field you can fill the files with a NULL value like:

INSERT INTO tablename SET customer_id = NULL WHERE ;

If you want to put a 0 value into that field:

INSERT INTO tablename SET customer_id = 0 WHERE ;

3
  • $q = $mysqli->query("SELECT * FROM oldTablename"); ... $sub_total = ($row["sub_total"] == '' ? 0 : str_replace( ',', '', $row["sub_total"]));....echo "INSERT INTO newTableName SET id = NULL, sub_total = '".$sub_total."', - I was hoping not to do $row["sub_total"] == '' ? 0
    – anjanesh
    Mar 30, 2023 at 7:07
  • Maybe: ... $sub_total = ($row["sub_total"] == '' ? 'NULL' : str_replace( ',', '', $row["sub_total"])); ....echo "INSERT INTO newTableName SET id = NULL, sub_total = ".$sub_total.", will work? (remove the quotes in the INSERT as $sub_total as it is a number, not a string),
    – Martie
    Mar 30, 2023 at 7:20
  • Well: - I was hoping not to do $row["sub_total"] == '' ? 0 sorry for that ;)
    – Martie
    Mar 30, 2023 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.