CODEWITHSUNDEEP
mysql
bbe
bbe

Reputation: 344

MySQL insert multiple rows with some values missing

Given

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

is a standard form for insert, how to insert multiple rows, if some data is missing?

e.g.

INSERT INTO tbl_name (a,b,c) VALUES(1,'missing','missing'),(4,'missing',6),(7,8,9);

Note, table may contain data instead for "missing" values, and it should not be overwritten with "null" or else.

Upvotes: 3

Views: 2235

Answers (3)

user7941334
user7941334

Reputation:

First, about what you specified:

Note, table may contain data instead for "missing" values, and it should not be overwritten with "null" or else.

In this regard, if you use only 'INSERT` statements, then it doesn't matter if some data exists already in some form, because an 'INSERT' statement inserts (!) new records, doesn't update records.

More of it: let's say you have an id column as a primary key column. If you try to INSERT a record with an 'id' identical with an existing one, then an error will arise: "Duplicate key...".

Now, let's say id is not a primary key value. Then, when you try to INSERT a new record with an 'id' identical with an existing one, then the new record will be inserted as duplicate.

That said, you can use UPDATE in order to update existing records. In order to not overwrite existing values, you can just omit them in UPDATE statement.

Example: table users with columns id, fname, lname:

id   fname   lname
1    John    Smith
2    Sam     Stevenson

UPDATE statement:

UPDATE users
SET fname='Helen'
WHERE id = 2;

Results:

id   fname   lname
1    John    Smith
2    Helen   Stevenson

Upvotes: 0

Vatev
Vatev

Reputation: 7590

If you want the same behavior as if the column was omitted in the insert use default:

INSERT INTO tbl VALUES (1,default,default),(4,default,6)...

If you want empty values just use null:

INSERT INTO tbl VALUES (1,null,null),(4,null,6)...

Upvotes: 2

Harsh Sharma
Harsh Sharma

Reputation: 930

In that case you can insert null instead of missing :

INSERT INTO tbl_name (a,b,c) VALUES(1,null,null),(4,null,6),(7,8,9);

This solution will work only if the target fields are nullable or it will throw error

Upvotes: 0

Related Questions