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. It's really urgent. Thanks in advance.

Answer 1

You should rather use UPDATE if you want to change the value of the field in records you select using 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

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.