Query for DELETE and UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query for DELETE and UPDATE

Author

Topic

djamit
Starting Member

17 Posts

Posted - 2013-08-05 : 10:59:49

I am trying to write a query to first delete the rows of a table and then update the table with new records which is imported from other database.
What is the best way to do this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 11:24:43

If you meant that you want the two operations - deleting existing data and then inserting new records - to be atomic, then open a transaction within a try/catch block. There are examples on the link below. Experiment with that and make sure you understand what it is trying to do before you implement it for your actual problem and then roll it out to a production environment. http://technet.microsoft.com/en-us/library/ms175976.aspx

Example C on that page is usually what I end up using most often.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-05 : 11:29:51

Delete the rows:
[CODE]
USE [AdventureWorks2012]
GO

DELETE FROM [dbo].[NewOrders]
WHERE <Search Conditions,,>
GO
[/CODE]

Then insert the new records using either SELECT INTO or bulk insert commands:
[CODE]
EXAMPLE:
SELECT orderid, orderdate, empid, custid INTO dbo.NewOrders FROM dbo.Orders;

OR
BULK INSERT dbo.NewOrders FROM 'c:\temp\neworders.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\ n' );

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 00:48:41

or use MERGE which will do deletion , updation and also insertion (if you want) in single statement

something like


MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
  UPDATE SET DestTableCol = s.SourceCol,
             DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY TARGET THEN
  DELETE
WHEN NOT MATCHED BY SOURCE THEN
 INSERT (Col1,Col2,..)
 VALUES (s.Col1,s.Col2,..);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 04:17:38

quote:

Originally posted by visakh16

MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
  UPDATE SET DestTableCol = s.SourceCol,
             DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY TARGET SOURCE THEN
  DELETE
WHEN NOT MATCHED BY SOURCE TARGET THEN
 INSERT (Col1,Col2,..)
 VALUES (s.Col1,s.Col2,..);

There is a note to this approach. The MERGE is an "all or nothing" approach.
Using the code above will delete all rows in destination table (including all historical rows) that not currently present in the source table.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 05:39:32

quote:
Originally posted by SwePeso

quote:
Originally posted by visakh16

MERGE DestinationTable AS d
USING SourceTable AS s
ON s.RelatedColumn = d.RelatedCol
WHEN MATCHED THEN
UPDATE SET DestTableCol = s.SourceCol,
DestTableCol1 = s.SourceCol2,... othercolumns
WHEN NOT MATCHED BY ~~TARGET~~ SOURCE THEN
DELETE
WHEN NOT MATCHED BY ~~SOURCE~~ TARGET THEN
INSERT (Col1,Col2,..)
VALUES (s.Col1,s.Col2,..);
There is a note to this approach. The MERGE is an "all or nothing" approach.
Using the code above will delete all rows in destination table (including all historical rows) that not currently present in the source table.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

We can always specify any additional condition you want to make sure DELETE only happens when that condition is satisfied by adding conditions by means of AND with NOT MATCHED BY SOURCE and TARGET clauses
What I gave was just a stub so in actual case OP might need to extend to suit the specific needs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 05:55:29

Which condition is that Visakh?
See this repro. What happens to "Two" and "Three"?

OP can be in the situation that the source table DOES have all the latest information. If so, the NOT MATCHED BY SOURCE AND DELETE could be ok.
But if the source table is only the delta (new and changed rows since last time), the DELETE is dangerous.

DECLARE	@Target TABLE
	(
		Data VARCHAR(100) PRIMARY KEY CLUSTERED,
		Information VARCHAR(100) NOT NULL
	);

INSERT	@Target
	(
		Data,
		Information
	)
VALUES	('One', 'Info 1'),
	('Two', 'Info 2'),
	('Three', 'Info 3');

-- Before
SELECT	Data,
	Information
FROM	@Target;

MERGE	@Target AS tgt
USING	(
		VALUES	('One', 'Second revision'),
			('Four', 'First draft')
	) AS src(Data, Information) ON src.Data = tgt.Data
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Information = src.Information
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					Data,
					Information
				)
			VALUES	(
					src.Data,
					src.Information
				)
WHEN	NOT MATCHED BY SOURCE
		THEN	DELETE;

-- After
SELECT	Data,
	Information
FROM	@Target;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 06:03:44

Ok ..I got that part
What I thought was OP wanted to do delete under some specific conditions seeinng this


DELETE FROM [dbo].[NewOrders]
      WHERE <Search Conditions,,>

If thats case the same conditions can be checked in
NOT MATCHED BY SOURCE caluse by means of and condition

like


...
WHEN	NOT MATCHED BY SOURCE AND <Search Conditions,,>
		THEN	DELETE
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djamit
Starting Member

17 Posts

Posted - 2013-08-06 : 07:22:11

The situation is like this: There is a table (MQ) where records are added daily. In a other table(CON) I want to insert the updated records in a scheduled job. I tought to delete all rows for CON first and then insert the the old records with the updated records from MQ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 08:14:44

What you then want, is to make a condition for the update.

WHEN MATCHED AND target.SomeDateTimeColumn < source.SomeDateTimeColumn

in order make sure you have the "latest version" of the row.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

djamit
Starting Member

17 Posts

Posted - 2013-08-06 : 11:40:27

I have solve this by:
TRUNCATE TABLE CON
GO
INSERT into dbo.CON
Select MQ.PAT_NAME,MQ.IDA,MQ.MD_INITIALS
from MQ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 11:55:51

quote:
Originally posted by djamit

I have solve this by:
TRUNCATE TABLE CON
GO
INSERT into dbo.CON
Select MQ.PAT_NAME,MQ.IDA,MQ.MD_INITIALS
from MQ

This would work in most cases and is the simplest perhaps. The problem that everyone who replied have been trying to solve is to preserve the atomicity. Consider this:

Your truncate table statement succeeds.
But then the insert statement fails. It can fail for a variety of reasons, usually due to bad data such as null values in non-nullable columns, or foreign key constraint violations, or simply plain bad data.

If that happens, if you follow the approach you are taking, you end up with no data in the table. If that is an acceptable or expected behavior, then what you are doing is the right thing to do. If that is not the case, consider the other approaches posted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 12:08:31

Or wrap a transaction around the two statements.

BEGIN TRY
	BEGIN TRAN
	
	TRUNCATE TABLE dbo.Con

	INSERT	dbo.CON
		(
			Name,
			Ida,
			Initials
		)
	SELECT	PAT_NAME,
		IDA,
		MD_INITIALS
	FROM	dbo.MQ
	
	COMMIT TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN
END CATCH

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

djamit
Starting Member

17 Posts

Posted - 2013-08-06 : 12:49:51

THANK YOU VERY MUCH SWEPESO
THIS WORKS FOR ME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources