SQL Insert Query With Condition

Question

I am trying to insert values into 1 column of a table when a condition is satisfied.

Note: The table already contains data for all the columns but for 1 which is empty. I would like to insert value into this 1 column depending on the WHERE clause.

I have this query:

INSERT INTO <TABLE_NAME>
(COLUMN_NAME)
(VALUE)
WHERE <CONDITION>

I am getting an exception:

Incorrect Syntax Near WHERE Keyword

I am able to do this using UPDATE:

UPDATE <TABLE_NAME>
SET <COL_NAME>
WHERE <CONDITION>

But was wondering why the INSERT query was failing. Any advise appreciated.

Answer 1

As I understand your problem, you already have data in one row, and one column in that row does not have value, so you want to add value in to that column.

This the scenario for Update existing row, not the insert new row. You have to use UPDATE clause when data already present and you want to modify record(s). Choose insert when You want to insert new row in table.

So in your current scenario, Update Clause is your friend with Where Clause as you want to modify subset of records not all.

UPDATE <TABLE_NAME>
SET <COL_NAME>
WHERE <CONDITION>

INSERT Clause does not have any Where Clause as per any RDBMS syntax(I think). Insert is condition less sql query, While SELECT, UPDATE, DELETE all are conditional commands, you can add Where Clause in all later ones.

Answer 2

In order to add a value into the one column when the rows are already populated, you will need to use the update statement.
If you need to insert a new row that has a where clause, you will need to use an insert into select statement:

INSERT INTO <table> (<columns>)
SELECT <columns>
FROM <table>
WHERE <condition>;