lyfz_repos/经典版源码/document/数据库维护脚本/删除2014开头的订单编号的全部信息.sql

USE [db]
-- 定义变量;
DECLARE @COUNT INT				-- 数量;
DECLARE @OrderId NVARCHAR(80)	-- 订单号;

-- 查询出所有订单的电话,并插入到临时表中;
SELECT IDENTITY(INT,1,1) AS AutoId, id INTO #temp_dindan FROM [dbo].[dindan] WHERE id like '2014%'
-- 获取临时表最大ID;
SELECT @COUNT = MAX(AutoId) FROM #temp_dindan

-- 循环将dindan表里的订单电话更新到client对应的订单记录中;
WHILE @COUNT > 0
BEGIN
	SELECT @OrderId = id FROM #temp_dindan WHERE AutoId = @COUNT
	DELETE FROM [dbo].[client] WHERE id = @OrderId						-- 订单顾客表;
	DELETE FROM [dbo].[dindan] WHERE id = @OrderId						-- 订单表;
	DELETE FROM [dbo].[dindansp] WHERE id = @OrderId					-- 订单商品表;
	DELETE FROM [dbo].[dindansp2] WHERE id = @OrderId					-- 订单商品表;
	DELETE FROM [dbo].[dindansp3] WHERE id = @OrderId					-- 订单商品表;
	DELETE FROM [dbo].[dindanbukuan] WHERE id = @OrderId				-- 订单补款表;
	DELETE FROM [dbo].[dindanbukuan2] WHERE id = @OrderId				-- 订单补款表;
	DELETE FROM [dbo].[burncdreg] WHERE id = @OrderId					-- 订单刻录表;
	DELETE FROM [dbo].[digitalwork] WHERE id = @OrderId					-- 订单数码安排表;
	DELETE FROM [dbo].[mywork] WHERE id = @OrderId						-- 我的订单工作表;
	DELETE FROM [dbo].[digitalwork] WHERE id = @OrderId					-- 订单冲印表;
	SET @COUNT = @COUNT -1
END

-- 最后,清除临时表;
DROP TABLE #temp_dindan