How to insert with where clause

Question

I am using the following query to insert values into one field in table A.

insert
into A (name)
values ('abc')
where
   A.id=B.code
   and B.dept='hr' ;

Is this query right? If not, please provide me a correct way.

Answer 1

You should rather use UPDATE, if you want to change the value of the field in records you select using the WHERE clause.

Answer 2

MERGE
INTO a
USING (
  SELECT *
  FROM b
  WHERE b.dept = 'hr'
)
ON a.id = b.code
WHEN NOT MATCHED THEN
  INSERT (id, name)
  VALUES (code, 'abc')
WHEN MATCHED THEN
  UPDATE
  SET name = 'abc'

Answer 3

INSERT INTO A (name)
SELECT name 
FROM A as a
LEFT JOIN B as b ON b.id=a.id
WHERE a.id=b.code AND b.dept='hr'

A is the first Table then B is the Second table JOIN together using LEFT JOIN then name is the column. this is what he wants to do on his question.

This is what "INSERT SELECT STATEMENT" do.

Query that can Insert while doing a select and combining a WHERE and JOIN.

INSERT TABLE1 (column1)
SELECT column1
FROM TABLE1 as a
LEFT JOIN TABLE2 as b ON b.column1=a.column1
WHERE a.column2=b.column3 AND b.column4='hr'

Answer 4

the insert part should be ok

insert
into A (name)
values ('abc')

it really depends on what you are trying to achieve with the where clause and the B table reference.