Hi, i want to know how update a row if exist and if don't exist insert a new row
Example:
Hi, i want to know how update a row if exist and if don't exist insert a new row
Example:
Is it supposed to be used in a multi-tasking environment (when more programs access the table in parallel)?
If you only want to update one row of data in a table, you can use IF statement:
IF EXISTS (SELECT 1 FROM Tbl WHERE UniqueColumn = 'Something')
BEGIN
UPDATE Tbl
SET ...
WHERE UniqueColumn = 'Something';
END
ELSE
BEGIN
INSERT INTO Tbl
SELECT ...
END
If you want to INSERT / UPDATE a destination table from on a source table, you can use a MERGE statement:
MERGE Tbl2 AS t
USING (
SELECT * FROM tb1
) AS s ON t.UniqueColumn = s.UniqueColumn
WHEN MATCHED THEN
UPDATE SET
t.Col1 = s.Col,
...
WHEN NOT MATCHED THEN
INSERT (
Col1,
Col2,
...
)
VALUES (
s.Col1,
s.Col2,
...
);
Hi @EvansGxz-8658,
Welcome to Microsoft Q&A!
For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.
Please also refer below one simple example and hope it could be helpful to you.
--DDL and insert sample data
drop table if exists test
create table test
(Testno int primary key,
name varchar(20),
age int)
insert into test values
(111,'Ann',19),
(222,'Bob',20),
(333,'Cathy',21)
select * from test
Using exists as below:
UPDATE T SET --update
name = 'Amy',
age = 19
FROM
test AS T
WHERE
T.testno = 111
INSERT INTO test ( --insert
Testno,
name,
age)
SELECT
id = 555,
name = 'Elan',
age = 19
WHERE
NOT EXISTS (SELECT 1 FROM test AS T WHERE Testno = 555)
Or using Merge statement as mentioned by Guoxiong.
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
15 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index