56

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
TheDelChop
  • 7,938
  • 4
  • 48
  • 70

7 Answers7

112

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

Brad Christie
  • 100,477
  • 16
  • 156
  • 200
  • 1
    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). – Michael Apr 27 '17 at 20:57
  • Use `SET SQL_SAFE_UPDATES = 0;` if the `Workbench` issues any errors – Arefe Sep 03 '17 at 10:04
27

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.

DVK
  • 126,886
  • 32
  • 213
  • 327
  • 2
    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. – Brad Christie Jan 14 '11 at 16:22
  • Ironically enough, I just gave you +1 for the comprehensive answer and especially reference link :) – DVK Jan 14 '11 at 16:29
  • @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. – Déjà vu Jan 14 '11 at 16:50
  • @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: – Brad Christie Jan 14 '11 at 17:14
  • @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. – Martin Smith Jan 14 '11 at 17:18
4

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

UPDATE table_name SET table_column = 'test';
Sani Kamal
  • 1,208
  • 16
  • 26
2

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';
Nicolas Buduroi
  • 3,565
  • 1
  • 28
  • 29
1
UPDATE `table` SET table_column='test';
Damodharan R
  • 1,497
  • 7
  • 10
1

The SQL you need is:

Update table set table_column = "test";

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

Hamza Zafeer
  • 2,360
  • 13
  • 30
  • 42
James Lelyveld
  • 294
  • 1
  • 3
  • 11
1

To create a new empty column and fill it with the same value (here 100) for every row (in Toad for Oracle):

ALTER TABLE my_table ADD new_column INT;
UPDATE my_table SET new_column = 100;
jeppoo1
  • 650
  • 1
  • 10
  • 23