Insert the same fixed value into multiple rows

问题:

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?

回答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).

回答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.

回答3:

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';

回答4:

UPDATE `table` SET table_column='test';

回答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.