The CREATE TABLE [dbo]. # t1 (
T a DECIMAL (18, 2),
L a DECIMAL (18, 2),
A DECIMAL (18, 2),
Q INT,
Formula NVARCHAR (999),
A DECIMAL value (18, 2)
)
Numerical
-INSERT INTO # t1 (T, L, A, Q, formula) VALUES (3200384, 3300, 5, 'CASE WHEN A
- implementation effect:
According to the formula written in a column formula to calculate the value, fill out
to the value of this column
Looking for help, thank you
CodePudding user response:
- calculating
Declare @ t a decimal (18, 2), @ l decimal (18, 2), @ a decimal (18, 2), @ qq int, @ gs nvarchar (999),
@ TSQL nvarchar (2000)
Declare the ap insensitive cursor for
Select the T, L, A, Q, formula from # t1
The open ap
The fetch first from the ap into @ t, @ l, a, @ @ q, @ gs
While (@ @ fetch_status & lt;> 1)
The begin
Select @ TSQL=N 'update # t1 set value=' + @ gs
+ N 'where T=+ cast (@ T as nvarchar (100))
+ N 'and L=' + cast (@ L as nvarchar (100))
+ N 'and A=' + cast (@ as A nvarchar (100))
+ N 'and Q=' + cast (@ Q as nvarchar (100))
The exec (@ TSQL)
The fetch next from the ap into @ t, @ l, a, @ @ q, @ gs
End
Close the ap
Deallocate ap
- the results
Select * from # t1
CodePudding user response:
Gonna try this, it is better to have a primary key, multiple batch processing can only think of circulation and cursor
DECLARE @ SQL VARCHAR (8000)
DECLARE @ ID INT
The SET @ ID=2
; WITH CTE
AS
(SELECT *, ROW_NUMBER () OVER (ORDER BY 1) (SELECT) AS an RN # FROM T1)
SELECT SQL='@ WITH CTE
AS
(SELECT *, ROW_NUMBER () OVER (ORDER BY 1) (SELECT) AS an RN # FROM T1)
The UPDATE CTE SET value='+ +' formula WHERE RN='+ CAST (@ ID AS VARCHAR)
The FROM CTE WHERE RN=@ ID
The EXEC (@ SQL)
CodePudding user response:
If the formula of all kinds of small according to different formula to update can:
- table structure
The CREATE TABLE [dbo]. # t1
(
T a DECIMAL (18, 2),
L a DECIMAL (18, 2),
A DECIMAL (18, 2),
Q INT,
Formula NVARCHAR (999),
A DECIMAL value (18, 2)
);
Numerical
-INSERT INTO # t1 (T, L, A, Q, formula) VALUES (3200384, 3300, 5, 'CASE WHEN AINSERT INTO # t1 (T, L, A, Q, formula) VALUES (8, 10, 0, 5, 'CASE WHEN A
- variable
DECLARE @ I INT, @ n INT.
DECLARE @ gs VARCHAR (500);
DECLARE @ SQL VARCHAR (MAX);
- according to the formula the category classification
The SELECT ROW_NUMBER () OVER the rowid (ORDER BY formula), formula INTO # # tt FROM t1 GROUP BY formula;
- according to the formula categories traversal
SELECT @ I=MIN (the rowid), @ # n=MAX (rowid) FROM tt;
WHILE (@ I & lt;=@ n)
The BEGIN
Gs=formula FROM the SELECT @ # tt WHERE rowid=@ I;
SET @ SQL='# update t1 SET value=' gs + + @ 'where formula=' "+ @ gs +" '"'; - generation script
The EXEC (@ SQL); Execute the script, can print the effect
The SET @ I=@ I + 1;
END;
SELECT * FROM # t1;
