SQL Update question

Question

I am wondering how this can be achieved.

Let's say I have a table with two columns (IU(uniqueidentifier),(ID(int), SEL(char(1))

ID column has the following values in each row(ordered by IU):

0, 1, 2, 2, 0, 0, 1, 2, 2, 2, 0, 0, 4, 2, 2, 0, 0, 1, 2, 0, 0

I need to update column SEL with 'Y' for rows which are part of the group: 1, 2, 2, 2 ... (Starts With 1 and in the next rows thare are 2's. (Group 4, 2, 2 is not correct).

So in this example column: SEL should be:

null, Y, Y, Y, null, null, Y, Y, Y, Y, null, null, 4, 2, 2, null, null, Y, Y, null, null

Thanks!

The natural order of rows in a SQL table is not expected to be relied upon - you should use data to dictate the order of the rows. Also, I don't think you meant to use `null, null, 4, 2, 2, null` - shouldn't they be all `null` (where they aren't 'Y')? — Doug Kress, Aug 22 '11 at 07:12
@Doug, well my mistake. In Real table of course i Have a key, I sort the rows by. — John, Aug 22 '11 at 07:13

score 2 · Accepted Answer · answered Aug 22 '11 at 09:25

Here's a set-based approach.

DDL & sample data:

DECLARE @atable TABLE (
  UI uniqueidentifier DEFAULT NEWSEQUENTIALID(),
  ID int,
  SEL char(1)
);
INSERT INTO @atable (ID)
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 4 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT 0;

The UPDATE statement:

WITH marked AS (
  SELECT
    *,
    grp = CASE ID WHEN 0 THEN 0 ELSE 1 END
  FROM @atable
),
grouped AS (
  SELECT
    *,
    grpID = ROW_NUMBER() OVER (ORDER BY UI)
          - ROW_NUMBER() OVER (PARTITION BY grp ORDER BY UI)
  FROM marked
),
ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY grp, grpID ORDER BY UI)
  FROM grouped
)
UPDATE g
  SET SEL = CASE r.ID
    WHEN 0 THEN NULL
    WHEN 1 THEN 'Y'
    ELSE CAST(g.ID AS varchar)
  END
FROM grouped g
  INNER JOIN ranked r ON g.grp = r.grp AND g.grpID = r.grpID
WHERE r.rnk = 1;

The result of SELECT * FROM @atable after the update:

UI                                   ID          SEL
------------------------------------ ----------- ----
A4095E70-A0CC-E011-813B-20CF30905E89 0           NULL
A5095E70-A0CC-E011-813B-20CF30905E89 1           Y
A6095E70-A0CC-E011-813B-20CF30905E89 2           Y
A7095E70-A0CC-E011-813B-20CF30905E89 2           Y
A8095E70-A0CC-E011-813B-20CF30905E89 0           NULL
A9095E70-A0CC-E011-813B-20CF30905E89 0           NULL
AA095E70-A0CC-E011-813B-20CF30905E89 1           Y
AB095E70-A0CC-E011-813B-20CF30905E89 2           Y
AC095E70-A0CC-E011-813B-20CF30905E89 2           Y
AD095E70-A0CC-E011-813B-20CF30905E89 2           Y
AE095E70-A0CC-E011-813B-20CF30905E89 0           NULL
AF095E70-A0CC-E011-813B-20CF30905E89 0           NULL
B0095E70-A0CC-E011-813B-20CF30905E89 4           4
B1095E70-A0CC-E011-813B-20CF30905E89 2           2
B2095E70-A0CC-E011-813B-20CF30905E89 2           2
B3095E70-A0CC-E011-813B-20CF30905E89 0           NULL
B4095E70-A0CC-E011-813B-20CF30905E89 0           NULL
B5095E70-A0CC-E011-813B-20CF30905E89 1           Y
B6095E70-A0CC-E011-813B-20CF30905E89 2           Y
B7095E70-A0CC-E011-813B-20CF30905E89 0           NULL
B8095E70-A0CC-E011-813B-20CF30905E89 0           NULL

One more question. Would be easy to change the script so that last 'Y' in the group could be N instead of Y? — John, Aug 22 '11 at 10:21
As I understand, it should be as easy as replacing `'Y'` with `'N'` in the CASE expression. — Andriy M, Aug 22 '11 at 11:00

score 1 · Answer 2 · edited May 23 '17 at 11:47

1

Rows in a table have no inherent order, so your grouping (1,2,2,2) is completely arbitrary. It is not guaranteed that your id's will always come in this order:

0, 1, 2, 2, 0, 0, 1, 2, 2, 2, 0, 0, 4, 2, 2, 0, 0, 1, 2, 0, 0

It could be that they come in a completely other order. So you need to specify a ORDER BY clause to get your order. As you have no other fields in your table but SEL and ID, I suppose this is not possible.

edited May 23 '17 at 11:47

Community

1
1

answered Aug 22 '11 at 07:14

Jacob

41,721
6
79
81

I have added to this example the key column. – John Aug 22 '11 at 07:15

John N · Answer 3 · 2011-08-22T08:15:23.223

I really hope someone comes up with something better than this, because I hate this answer.

create table #test (
    IU  int identity    primary key,
    id  int,
    sel varchar(1)
)

insert into #test(id)
values (0), (1), (2), (2), (0), (0), (1), (2), (2), (2), (0), (0), (4), (2), (2), (0), (0), (1), (2), (0), (0)

DECLARE myCur CURSOR FORWARD_ONLY
FOR
    select t.ID
    from #test t
    order by t.IU
FOR UPDATE OF t.sel

DECLARE @ID int, @lagSel varchar(1)

OPEN myCur
FETCH myCur INTO @ID

WHILE (@@FETCH_STATUS = 0) BEGIN
    SET @lagSel = CASE 
                    WHEN @lagSel = 'Y' AND @ID in (1,2) THEN 'Y'
                    WHEN @ID = 1 THEN 'Y'
                    ELSE NULL
                  END

    UPDATE #test
    SET sel = @lagSel
    WHERE CURRENT OF myCur

    FETCH myCur INTO @ID
END

CLOSE myCur
DEALLOCATE myCur

A couple of things to note:

We're manually managing the value of @lagSel within the cursor so we can carry a value from one row to the next.
In order to be able to use the cursor FOR UPDATE, the table has to have a primary key.
In the UPDATE statement, the WHERE CURRENT OF myCur gives (at least in theory) a big performance gain over any other where clause.

I first tried doing this with lagged joins, but couldn't quite get it there. Here's my work in case someone else can do better:

select main.IU, main.id,
    CASE 
        WHEN main.id = 1 THEN 'Y'
        WHEN main.id = 2 AND lag.id in (1, 2) THEN 'Y'
        ELSE NULL
    END as new_sel
from #test main left outer join
    #test lag on main.IU = lag.IU + 1

+1 for the fortitude to post a hated answer. :) Just joking. Actually I can see nothing wrong with your cursor solution, and your notes make it really useful. In particular, I've never employed cursors for updating and I find your query a good and clear example of how that can be carried out. — Andriy M, Aug 22 '11 at 11:19
Cheers, @Andriy. Yes, while the answer works just fine, it's performance on a very large table would be pretty poor in comparison to your set-based example. However, it doesn't hurt my brain in quite the same way. ;-) — John N, Aug 22 '11 at 13:27

Alex_L · Answer 4 · 2011-08-22T07:31:34.227

0

I think you have the design error here. MS SQL Server does not knows nothing about "next" and "previous" rows. if you try to select the records, the order of the records can be changed from time to time, unless you specify the ordering using ORDER BY statement. I think you need to change the structure of the tables first.

EDIT: As I see, you have the field and can order your records. Now you can achive your goal using CURSOR. Briefly, you can create the CURSOR FOR SELECT IU, ID ORDER BY IU ASC. looping through the cursor records you can check the sequence of the values of ID field, and when sequence will be fully equivalent, you can update the corresponding record.

edited Aug 22 '11 at 07:31

answered Aug 22 '11 at 07:15

Alex_L

2,658
1
15
13

I have added to this example the key column. And Let's say that sorted rows by IU give the input values in my question. – John Aug 22 '11 at 07:20
I edited the post, see my edit. This is ugly solution, but it must work. – Alex_L Aug 22 '11 at 07:32
Yep, I am always trying to avoid cursors. If the table is large it is not efficient. But thanks for your suggestion. Such problems generally can be solved by joining the same table twice with the next IU, however here, there are more than two records to check... – John Aug 22 '11 at 07:40
I'm agreed, this solution is UGLY :) joining of 4 tables is the ugly too, but I cannot see another solution. – Alex_L Aug 22 '11 at 07:58

SQL Update question

4 Answers4