Insert the same fixed value into multiple rows

Question

I've got a table with a column, lets call it table_column that is currently null for all rows of the table. I'd like to insert the value "test" into that column for all rows. Can someone give me the SQL for this?

I've tried INSERT INTO table (table_column) VALUES ("test"); but that only populates that last row. How do I do all of the rows at once?

Solution 1

You're looking for UPDATE not insert.

UPDATE mytable
SET    table_column = 'test';

UPDATE will change the values of existing rows (and can include a WHERE to make it only affect specific rows), whereas INSERT is adding a new row (which makes it look like it changed only the last row, but in effect is adding a new row with that value).

Solution 2

This is because in relational database terminology, what you want to do is not called "inserting", but "UPDATING" - you are updating an existing row's field from one value (NULL in your case) to "test"

UPDATE your_table SET table_column = "test" 
WHERE table_column = NULL 

You don't need the second line if you want to update 100% of rows.

Solution 3

To update the content of existing rows use the UPDATE statement:

UPDATE table_name SET table_column = 'test';

Solution 4

What you're actually doing is adding rows. To update the content of existing rows use the UPDATE statement:

UPDATE table SET table_column = 'test';

Solution 5

The SQL you need is:

Update table set table_column = "test";

The SQL you posted creates a new row rather than updating existing rows.

View more solutions

Comments

[Brad Christie]

I gave you +1 just because you're about the only other one that posted an answer and a reason. No one on SO seems to post reasoning or references any more, they want to be first to the draw.

[DVK]

Ironically enough, I just gave you +1 for the comprehensive answer and especially reference link :)

[Déjà vu]

@Brad To be fair, you also posted the raw answer only, initially - then you edited your post to add some explanations. When I sent my answer, yours was 30" ahead, and had already 2 upvotes... So you are right, the first in the draw get the upvote for that kind of question.

[Brad Christie]

@ring0: You are correct, but more or less fat-fingered and pre-submitted the form. I still feel it's important to give further explanation on questions such as this. The old "teach a man to fish" adage, not "here, use this--this will do what you want". I sometimes feel people vote too quickly on the working not, not the best quality post, but not much I can do about it. I've seen posts with 10 up-votes and just working code succeed and become an answer, when one with no upvotes and a descriptive answer remains at the bottom. :shrug:

[Martin Smith]

@ring0 - Yep that is true. Best strategy is to post a short (but meaningful and correct) answer straight away then perhaps edit it to add in a few "optional extra" points. Voting patterns don't favour those who compose a full answer before posting for this type of easy question.

[Michael]

Found the question because I actually do want to insert multiple rows with all the same column values (except the autoincremented primary key, which i wouldn't provide in an insert anyway).

[Arefe]

Use SET SQL_SAFE_UPDATES = 0; if the Workbench issues any errors