How to Insert with Where Clause
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...
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.
4 Answers
You should rather use UPDATE, if you want to change the value of the field in records you select using the WHERE clause.
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'
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.
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'
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
