CODEWITHSUNDEEP
sql-server
envyM6
envyM6

Reputation: 1237

INSERT statement has incorrect syntax near WHERE keyword. Why?

What would be the update statement for all records WHERE USID = 49?

This is what I currently have:

INSERT INTO ADDRESSES (AddressLine1, AddressLine2, PostCode) 
VALUES ('1A','2B', '3C') WHERE USID = 49;

But I'm getting an error "Incorrect syntax near the keyword WHERE." What am I doing wrong here?

Upvotes: 0

Views: 762

Answers (1)

Hogan
Hogan

Reputation: 70513

TL;DR:

INSERT is used when you want to create new records in a table. It appears that you do not actually want to add new records. If you want to modify existing records, use UPDATE instead:

UPDATE ADDRESSES
  SET AddressLine1 = '1A', AddressLine2 = '2B', PostCode = '3C'
 WHERE USID = 49;

Explanation why INSERT … WHERE is invalid syntax:

The purpose of a WHERE clause is to exclude all rows but those that meet the specified condition. When INSERT-ing, there is no need to look at any existing rows at all; you want to create new rows, and these are completely independent from any existing records. Therefore INSERT does not require WHERE clause. In fact it doesn't allow it because it wouldn't make any sense at all.

With UPDATE, on the other hand, a WHERE clause makes sense because you are dealing with existing rows, and you might not want to change all existing rows, but only a subset. The WHERE clause allows you to define the subset of rows that will be updated.

Upvotes: 5

Related Questions