22,300
社区成员




--update
UPDATE W
SET W.price2 = CASE
WHEN W.is_ew = 0 THEN E.price
ELSE E.price -2.0000
END,
W.price2_time = CASE
WHEN W.price2_time = 0 THEN -1536366010
ELSE W.price2_time
END
FROM [waybill] W
INNER JOIN [area] A
ON W.customer_id = 331
AND W.daybill_id = 591
AND W.destination = A.destination
AND W.real_weight > 0
AND W.real_weight <= 1000
INNER JOIN [price_p] P
ON P.price_id = 317
AND A.province_id = P.province_id
INNER JOIN [price_w] E
ON E.price_id = 317
AND E.[catalog] = P.[catalog]
AND E.[weight] = 1000
AND E.price > 0
--query
SELECT W.price2,
CASE
WHEN W.is_ew = 0 THEN E.price
ELSE E.price -2.0000
END,
W.price2_time,
CASE
WHEN W.price2_time = 0 THEN -1536366010
ELSE W.price2_time
END
FROM [waybill] W
INNER JOIN [area] A
ON W.customer_id = 331
AND W.daybill_id = 591
AND W.destination = A.destination
AND W.real_weight > 0
AND W.real_weight <= 1000
INNER JOIN [price_p] P
ON P.price_id = 317
AND A.province_id = P.province_id
INNER JOIN [price_w] E
ON E.price_id = 317
AND E.[catalog] = P.[catalog]
AND E.[weight] = 1000
AND E.price > 0
BEGIN TRAN
BEGIN TRY
--表变量的主键,字段类型你按实际的来修改
DECLARE @t TABLE (
w主键 INT PRIMARY KEY,
price2 DECIMAL(18,4),
price2_time BIGINT
)
INSERT INTO @t(w主键,price2,price2_time)
SELECT W.主键, --自己改
CASE
WHEN W.is_ew = 0 THEN E.price
ELSE E.price -2.0000
END AS price2,
CASE
WHEN W.price2_time = 0 THEN -1536366010
ELSE W.price2_time
END AS price2_time
FROM [waybill] W WITH (XLOCK,ROWLOCK)
INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK)
ON W.customer_id = 331
AND W.daybill_id = 591
AND W.destination = A.destination
AND W.real_weight > 0
AND W.real_weight <= 1000
INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK)
ON P.price_id = 317
AND A.province_id = P.province_id
INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK)
ON E.price_id = 317
AND E.[catalog] = P.[catalog]
AND E.[weight] = 1000
AND E.price > 0
UPDATE W
SET price2 = t.price2,price2_time = t.price2_time
FROM [waybill] W INNER JOIN @t t ON w.主键=t.w主键
COMMIT TRAN;
END TRY
BEGIN CATCH
DECLARE @errMsg NVARCHAR(MAX)
SET @errMsg=ERROR_MESSAGE()
RAISERROR(16,1,@errMsg)
ROLLBACK TRAN;
END CATCH
SELECT *
FROM sys.tables AS A WITH(NOLOCK) INNER JOIN
sys.columns AS B WITH(NOLOCK) ON a.object_id=b.object_id
UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0
SELECT CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0
UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0
UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0
BEGIN TRAN
BEGIN TRY
--表变量的主键,字段类型你按实际的来修改
DECLARE @t TABLE (
w主键 INT PRIMARY KEY,
price2 DECIMAL(18,4),
price2_time BIGINT
)
INSERT INTO @t(w主键,price2,price2_time)
SELECT W.主键, --自己改
CASE
WHEN W.is_ew = 0 THEN E.price
ELSE E.price -2.0000
END AS price2,
CASE
WHEN W.price2_time = 0 THEN -1536366010
ELSE W.price2_time
END AS price2_time
FROM [waybill] W WITH (XLOCK,ROWLOCK)
INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK)
ON W.customer_id = 331
AND W.daybill_id = 591
AND W.destination = A.destination
AND W.real_weight > 0
AND W.real_weight <= 1000
INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK)
ON P.price_id = 317
AND A.province_id = P.province_id
INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK)
ON E.price_id = 317
AND E.[catalog] = P.[catalog]
AND E.[weight] = 1000
AND E.price > 0
UPDATE W
SET price2 = t.price2,price2_time = t.price2_time
FROM [waybill] W INNER JOIN @t t ON w.主键=t.w主键
COMMIT TRAN;
END TRY
BEGIN CATCH
DECLARE @errMsg NVARCHAR(MAX)
SET @errMsg=ERROR_MESSAGE()
RAISERROR(16,1,@errMsg)
ROLLBACK TRAN;
END CATCH
BEGIN TRAN
BEGIN TRY
UPDATE W
SET W.price2 = CASE
WHEN W.is_ew = 0 THEN E.price
ELSE E.price -2.0000
END,
W.price2_time = CASE
WHEN W.price2_time = 0 THEN -1536366010
ELSE W.price2_time
END
FROM [waybill] W WITH (XLOCK,ROWLOCK)
INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK)
ON W.customer_id = 331
AND W.daybill_id = 591
AND W.destination = A.destination
AND W.real_weight > 0
AND W.real_weight <= 1000
INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK)
ON P.price_id = 317
AND A.province_id = P.province_id
INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK)
ON E.price_id = 317
AND E.[catalog] = P.[catalog]
AND E.[weight] = 1000
AND E.price > 0
COMMIT TRAN;
END TRY
BEGIN CATCH
DECLARE @errMsg NVARCHAR(MAX)
SET @errMsg=ERROR_MESSAGE()
RAISERROR(16,1,@errMsg)
ROLLBACK TRAN;
END CATCH