37

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
shin
  • 31,901
  • 69
  • 184
  • 271
  • 1
    If you really believe that: 1) Create a table, 2) immediately run an UPDATE statement 3) SELECT * from the table. – OMG Ponies Jan 04 '10 at 21:59

6 Answers6

53

In CRUD operations, the INSERT is the 'C' and the UPDATEis the 'U'. They are two of the four basic functions of persistent storage. The other two are SELECT and DELETE. Without at least these four operations, a typical database system cannot be considered complete.

Use INSERT to insert a new record.

Use UPDATE to update an existing record.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
49

You cannot UPDATE a row that's not in a table.

You cannot INSERT a row that's already in a table.

slebetman
  • 109,858
  • 19
  • 140
  • 171
  • 3
    What if you need to add data into a column that may or may not have data? Is there a forced insert mode, or some sort of INSERT-UPDATE hybrid? – Tom Dec 14 '16 at 21:47
  • In mysql, we have EXISTS keyword and IFNULL keyword to check wheter the referenced data is in a certain state. it would look like INSERT INTO table_name IFNULL follower by UPDATE table_name IF EXISTS. This approach may take more lines then desirable and honestly there may be a better solution, but at least it will work – Brakke Baviaan Oct 12 '21 at 13:01
4

Insert is for adding data to the table, update is for updating data that is already in the table.

Michael
  • 1,626
  • 11
  • 23
1

Insert is for putting in a fresh record to the table. while the update enables you to modify the inserted record e.g. modifying data type etc.

user3162187
  • 11
  • 1
1

An UPDATE statement can use a WHERE clause but INSERT cannot.

Tony
  • 9,672
  • 3
  • 47
  • 75
dsa
  • 35
  • 1
  • 1
    This is not true. There are examples of INSERT using WHERE at https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017 – James Jenkins Jan 11 '19 at 15:22
-1

Insert can be useful to insert new record in BLANK row. While Update can be used to update row which is NOT BLANK.

Niraj
  • 1