I am trying to update (or create) a column in a temporary table by assigning a variable and update that variable conditionally as well in SQL Server Management Studio.

I wrote the the following code but got an syntax error:

DECLARE @VARI INT = 0;

UPDATE #test1_b
SET Test_group = @VARI, 
    @VARI = CASE 
                WHEN TARGET_WEB = PREVIOUS 
                    THEN @VARI
                    ELSE @VARI   1 
            END;

Here is the error I got:

Msg 103010, Level 16, State 1, Line 15
Parse error at line: 3, column: 7: Incorrect syntax near '('.

I am very confused as I don't even include any '(' in my code. I was trying to follow this instruction: Inline Variable Assignment in UPDATE Statement. Below is the first few line and my expected outcome of my table:

Ultimately I am trying to group by the rows with consecutive webs values and sum up the total time that user stay at each web before they leave the page. Here is the desired outcome after aggregation:

I don't know if SQL allows people update the variables using variable itself in the UPDATE statement or not. Please help. Thank you.

CodePudding user response：

It seems You are trying to update field test_n with variable @vari and at the same time updating value of variable @vari. I suggest to update field instead of variable. Refer below code.

DECLARE @VARI INT = 0;

UPDATE #test1_b SET test_n = CASE WHEN TARGET_NUM = PREVIOUS THEN @VARI ELSE @VARI 1 END;

CodePudding user response：

The following will get you what you want based on your sampled data and expected results:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE #T (Test_group INT, TARGET_WEB VARCHAR(5), PREVIOUS VARCHAR(5), time INT, ExpectedGroup INT);

INSERT INTO #T(Test_group, TARGET_WEB, PREVIOUS, time, ExpectedGroup)
VALUES
    (1, 'web 1', 'Na', 1, 1),
    (1, 'web 2', 'web 1', 2, 2),
    (1, 'web 2', 'web 2', 3, 2),
    (1, 'web 1', 'web 2', 4, 3),
    (1, 'web 3', 'web 1', 5, 3);


SELECT  t.NewExpectedGroup,
        t.TARGET_WEB,
        SUM(t.time)
FROM    (   SELECT  Test_group, 
                    TARGET_WEB, 
                    PREVIOUS, 
                    time, 
                    ExpectedGroup,
                    NewExpectedGroup = SUM(CASE WHEN TARGET_WEB = [PREVIOUS] THEN 0 ELSE 1 END) OVER (ORDER BY [time])
            FROM    #T
        ) AS t
GROUP BY t.NewExpectedGroup, t.TARGET_WEB
ORDER BY t.NewExpectedGroup;

It works simply using a conditional windowed function, adding 1 to the sum only if TARGET_WEB <> PREVIOUS. The main thing to note here though, is that this requires some kind of order within the windowed function. In your example it works because time happens to be incremental, but in your actual scenario I'd guess this is very unlikely to be the case, and you'll need to use another column (presumably some kind of created time) to specify the order in which to apply your expected grouping.