how to update row based on value of another row sql server

Question

i would like to know what is the best way to update a row based on another row , lets say for example i have table like this:

ID   |   NAME    |  VALUE  |
----------------------------
 1   |   a  |   10   |
 2   |   b  |  NULL  |
 3   |   c  |  NULL  |
 4   |   d  |  NULL  |
 5   |   a  |   10   |
 6   |   b  |  NULL  |
 7   |   c  |  NULL  |
 8   |   d  |  NULL  |
 9   |   a  |  NULL  |
.
.
.

now i need an UPDATE query that will compare all the rows with the name = d to rows with name = a and if row with name = a have in value = 10 then row with name = d will get value = 10 and then row with name = a value will set to NULL and if not then not. its like i need to switch between the values of the rows (a to d , b to c , c to d , d to a) in that order. in every 4 rows(a,b,c,d) will allways be only one row that value is not null.

i hope it was clear. thanks!!

I think a better table structure would help a lot. For Example ID|a|b|c|d this would allow you to compare along the row for any given id. In it's current form i know that name d id 4 should be compared to name a id 1 but the sql logic for the databse would need to be unecessarily convoluted to make the correct comparisons and maintain — MarkD
– MarkD, Commented Jun 7, 2017 at 14:20

Richard Hansell · Accepted Answer · 2017-06-12 10:39:02Z

1

I don't think I fully understand what you are trying to achieve here, but here's something to start a discussion:

--Create sample data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;
CREATE TABLE #temp (ID INT, NAME VARCHAR(50), [VALUE] INT);
INSERT INTO #temp SELECT 1, 'a', 10;
INSERT INTO #temp SELECT 2, 'b', NULL;
INSERT INTO #temp SELECT 3, 'c', NULL;
INSERT INTO #temp SELECT 4, 'd', NULL;
INSERT INTO #temp SELECT 5, 'a', 10;
INSERT INTO #temp SELECT 6, 'b', NULL;
INSERT INTO #temp SELECT 7, 'c', NULL;
INSERT INTO #temp SELECT 8, 'd', NULL;
INSERT INTO #temp SELECT 9, 'a', NULL;

All this does is to swap a's and d's around if there is a value in the a's:

WITH Best AS (
    SELECT NAME, MAX([VALUE]) AS MAXVALUE FROM #temp GROUP BY NAME)
UPDATE
    t
SET
    [VALUE] = 
        CASE 
            WHEN t.NAME = 'a' AND b.MAXVALUE IS NOT NULL THEN NULL
            WHEN t.NAME = 'd' AND b.MAXVALUE IS NOT NULL THEN b.MAXVALUE 
        END
FROM
    #temp t
    CROSS JOIN Best b 
WHERE
    t.NAME IN ('a', 'd')
    AND b.NAME = 'a';

SELECT * FROM #temp;

On completion I can see that all the a values have moved to the d rows. That doesn't cope with b to c, c to d, etc. though. Is this anything like what you want?

Now it looks like you actually need each group of four rows to be handled separately, so this might work better?

WITH Groups AS (
    SELECT ID, (ID - 1) / 4 AS group_id, NAME, [VALUE] FROM #temp),
Best AS (
    SELECT group_id, NAME, MAX([VALUE]) AS MAXVALUE FROM Groups GROUP BY group_id, NAME)
UPDATE
    t
SET
    [VALUE] = 
        CASE 
            WHEN t.NAME = 'a' AND b.MAXVALUE IS NOT NULL THEN NULL
            WHEN t.NAME = 'd' AND b.MAXVALUE IS NOT NULL THEN b.MAXVALUE 
        END
FROM
    #temp t
    INNER JOIN Groups g ON g.ID = t.ID
    INNER JOIN Best b ON b.group_id = g.group_id
WHERE
    t.NAME IN ('a', 'd')
    AND b.NAME = 'a';

Here's a new query for you, now I finally think I understand what this is supposed to do. First here's my understanding of what you actually want:

you have data that is ordered by an ID;
the data is organised into groups of 4, starting at "a", then "b", then "c", then "d";
for each group of 4 items find the single item that has a non-NULL value;
move this one forward, i.e. if the a value is there move it to b, etc.
the d value moves back round to a again;
each group is independent of the others.

I created a slightly different set of data to show this working in practice:

--Create sample data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;
CREATE TABLE #temp (ID INT, NAME VARCHAR(50), [VALUE] INT);
INSERT INTO #temp SELECT 1, 'a', 10;
INSERT INTO #temp SELECT 2, 'b', NULL;
INSERT INTO #temp SELECT 3, 'c', NULL;
INSERT INTO #temp SELECT 4, 'd', NULL;
INSERT INTO #temp SELECT 5, 'a', NULL;
INSERT INTO #temp SELECT 6, 'b', NULL;
INSERT INTO #temp SELECT 7, 'c', NULL;
INSERT INTO #temp SELECT 8, 'd', 5;
INSERT INTO #temp SELECT 9, 'a', NULL;
INSERT INTO #temp SELECT 10, 'b', 90;
INSERT INTO #temp SELECT 11, 'c', NULL;
INSERT INTO #temp SELECT 12, 'd', NULL;

...and here's my amended query:

WITH Groups AS (
    SELECT 
        (ID - 1) / 4 AS group_id,
        ID, 
        NAME, 
        [VALUE] 
    FROM 
        #temp),
Best AS (
    SELECT 
        group_id, 
        ID,
        NAME,
        [VALUE]
    FROM 
        Groups
    WHERE
        [VALUE] IS NOT NULL)
UPDATE
    t
SET
    [VALUE] = 
        CASE 
            WHEN t.NAME = 'a' AND b.NAME = 'a' THEN NULL
            WHEN t.NAME = 'a' AND b.NAME = 'd' THEN b.[VALUE]
            WHEN t.NAME = 'b' AND b.NAME = 'b' THEN NULL
            WHEN t.NAME = 'b' AND b.NAME = 'a' THEN b.[VALUE]
            WHEN t.NAME = 'c' AND b.NAME = 'c' THEN NULL
            WHEN t.NAME = 'c' AND b.NAME = 'b' THEN b.[VALUE]
            WHEN t.NAME = 'd' AND b.NAME = 'd' THEN NULL
            WHEN t.NAME = 'd' AND b.NAME = 'c' THEN b.[VALUE]
        END
FROM
    #temp t
    INNER JOIN Groups g ON g.ID = t.ID
    INNER JOIN Best b ON b.group_id = g.group_id;

So my data starts off like this:

ID  NAME    VALUE
1   a   10
2   b   NULL
3   c   NULL
4   d   NULL
5   a   NULL
6   b   NULL
7   c   NULL
8   d   5
9   a   NULL
10  b   90
11  c   NULL
12  d   NULL

...and when I run the UPDATE query:

ID  NAME    VALUE
1   a   NULL
2   b   10
3   c   NULL
4   d   NULL
5   a   5
6   b   NULL
7   c   NULL
8   d   NULL
9   a   NULL
10  b   NULL
11  c   90
12  d   NULL

If I run the query again then everything moves one more step:

ID  NAME    VALUE
1   a   NULL
2   b   NULL
3   c   10
4   d   NULL
5   a   NULL
6   b   5
7   c   NULL
8   d   NULL
9   a   NULL
10  b   NULL
11  c   NULL
12  d   90

If this still isn't what you want then either show me what you think the values should be after the first/ second iteration, or come up with a new before -> after example in your question.

Hopefully this will be the final attempt :D

WITH Groups AS (
    SELECT 
        (ID - 1) / 4 AS group_id,
        ID, 
        NAME, 
        [VALUE] 
    FROM 
        #temp),
Best AS (
    SELECT 
        group_id, 
        ID,
        NAME,
        [VALUE]
    FROM 
        Groups
    WHERE
        [VALUE] IS NOT NULL)
UPDATE
    t
SET
    [VALUE] = 
        CASE 
            --a to d
            WHEN t.NAME = 'd' AND b.NAME = 'a' THEN b.[VALUE]

            --b to c
            WHEN t.NAME = 'c' AND b.NAME = 'b' THEN b.[VALUE] 

            --c to d (seems wrong?)
            WHEN t.NAME = 'd' AND b.NAME = 'c' THEN b.[VALUE]

            --d to a
            WHEN t.NAME = 'a' AND b.NAME = 'd' THEN b.[VALUE]

            --Set the moving item to NULL
            WHEN t.NAME = b.NAME THEN NULL
        END
FROM
    #temp t
    INNER JOIN Groups g ON g.ID = t.ID
    INNER JOIN Best b ON b.group_id = g.group_id;

edited Jun 12, 2017 at 10:39

answered Jun 7, 2017 at 14:28

Richard Hansell

5,4131 gold badge20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

anton Over a year ago

i did it on my server and it worked for the first time when a hade value 10 in row a and NULL in row d but when i tried to do this on the same rows 1-4 but with value NULL in row a it still put 10 in row d.

anton Over a year ago

ok this problem is solved , now there is another problem : i tried to put in row 1 value 10 and in row 7 value 10 all the other rows 2,3,4,5,6,8 have NULL , when i compilate row 1 got NULL , row 4 got 10 and row 8 got 10 wich is not good because row 8 need to be depandant not on row 1 but on row 5 and row 5 didnt hade value 10.

Richard Hansell Over a year ago

From this it sounds like you actually want the update to work on batches of four rows at a time, so rows 1-4 are a group, then rows 5-8, etc.? If so then my script won't work, as it looks across then entire set each time.

anton Over a year ago

much better now only one more problem : if in row a value is NULL then row d gets NULL TO.

Richard Hansell Over a year ago

I think I know how this should work now, here's another try

|

Collectives™ on Stack Overflow

how to update row based on value of another row sql server

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related