Insert the same fixed value into multiple rows

mysql sql sql-update sql-insert
TheDelChop picture TheDelChop · Jan 14, 2011 · Viewed 116k times · Source

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?

Answer

Brad Christie picture Brad Christie · Jan 14, 2011

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

Related questions

What are differences between INSERT and UPDATE in MySQL?

It seems INSERT and UPDATE do the same things to me. Is there any occasions where I should use INSERT instead of UPDATE and vice versa?

Read More
Fastest way to update a MySQL table if row exists else insert. More than 2 non-unique keys

I have the following table structure: CREATE TABLE IF NOT EXISTS `reports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `day` int(11) NOT NULL, `uid` int(11) NOT NULL, `siteid` int(11) NOT NULL, `cid` int(3) NOT NULL, `visits` int(11) NOT NULL, PRIMARY KEY (`…

Read More
Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 …

Read More