SQL Server: Update, set a value that is within another select

Question

I have the following query:

    update UpdCmmPartnerArticleGroup 
    set IsActive = 1,
    Name = a.GroupName
    from 
    (
       select t.Name as GroupName
       from  @ArticleGroupsTable t
       left join UpdCmmPartnerArticleGroup s on s.PartnerID=t.PartnerID and s.Name=t.Name
       where s.PartnerID is null
    ) a
    where Name = '' and IsActive = 0

It's purpose is to take a list of GroupNames and assign them to the UpdCmmPartnerArticleGroup.Name field. The list has 8 values and it uses only the first and the last to update.

What am I doing wrong, it looks pretty straight forward, but it's behavior is strange.

There's no need for the outer query, you can do the update directly with the inner join query — Allan S. Hansen, Aug 29 '16 at 12:56
duplicate. see http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — swe, Aug 29 '16 at 14:07

score 0 · Answer 1 · answered Aug 29 '16 at 13:02

0

Why not use subquery

update s
set IsActive = 1,
Name = (select t.Name from  @ArticleGroupsTable t WHERE s.PartnerID=t.PartnerID and s.Name=t.Name AND s.PartnerID is null)
from UpdCmmPartnerArticleGroup s
where Name = '' and IsActive = 0

answered Aug 29 '16 at 13:02

Jaydip Jadhav

12,179
6
24
40

1

This could work but needs to be the other way around. @ArticleGroupTable should be S and UpdCmmPartnerArticleGroup should be t, but will not compile. Anyway an upvote for a good suggestion. – Aug 29 '16 at 13:18

gofr1 · Accepted Answer · 2016-08-29T13:22:16.327

0

You can use a little bit simplified version.

UPDATE u
SET IsActive = 1, Name = t.Name
FROM @ArticleGroupsTable t
LEFT JOIN UpdCmmPartnerArticleGroup u
    ON u.PartnerID=t.PartnerID AND u.Name=t.Name
WHERE u.Name = '' and u.IsActive = 0 and u.PartnerID IS NULL

edited Aug 29 '16 at 13:22

answered Aug 29 '16 at 13:03

gofr1

15,741
11
42
52

I have tried that. What happens is that I have two similar tables. I am comparing them by using a left join. Ones that give out PartnerID = null on the left need to be used. This way it will only give me null values. – Aug 29 '16 at 13:13
Almost same LEFT join.. have you tried it? I put some edit in an answer. – gofr1 Aug 29 '16 at 13:16
I have. This is the same as before. It will still give me null values only. Logically u.Name = '' and u.IsActive = 0 and u.PartnerID IS NULL need to be in nested selects in order to give correct information. Mine query should work. However, when I use a.GroupName as value it only takes the first and the last from the inner select. No idea why. Thank you for the help! – Aug 29 '16 at 13:23
I rollback the edit because it was irrelevant. As I see now, you just get some values from `a` and update `UpdCmmPartnerArticleGroup` table where `Name = '' and IsActive = 0` with some first value from `a` I guess. Is that logic you need? Or there must be some other logic in this update? – gofr1 Aug 29 '16 at 13:33

StackUser · Answer 3 · 2016-08-29T13:37:04.563

0

Try this,

UPDATE s
SET s.IsActive = 1
    ,s.NAME = t.NAME
FROM @ArticleGroupsTable t
LEFT JOIN UpdCmmPartnerArticleGroup s ON s.PartnerID = t.PartnerID
    AND s.NAME = t.NAME
WHERE s.PartnerID IS NULL
    AND s.NAME = ''
    AND s.IsActive = 0

edited Aug 29 '16 at 13:37

answered Aug 29 '16 at 13:04

StackUser

5,370
2
24
44

I am using left join as a "comparer" between two tables. If one has a row that the other ones hasn't it gives me partner NULL, these are the ones I need to assign. Thanks but this returns only nulls for that reason. – Aug 29 '16 at 13:10

score 0 · Answer 4 · answered Aug 29 '16 at 14:04

Try this:

IF OBJECT_ID('tempdb..#UpdCmmPartnerArticleGroup') IS NOT NULL
    DROP TABLE #UpdCmmPartnerArticleGroup;
IF OBJECT_ID('tempdb..#ArticleGroupsTable') IS NOT NULL
    DROP TABLE #ArticleGroupsTable;
CREATE TABLE #UpdCmmPartnerArticleGroup
    (
     PartnerId INT IDENTITY(1, 1)
    ,Name VARCHAR(50)
    ,IsActive BIT
    );

INSERT INTO [#UpdCmmPartnerArticleGroup]
        ( [Name], [IsActive] )
    VALUES  ( 'Test1'  -- Name - varchar(50)
             , 1  -- IsActive - bit
             ),
            ( 'Test2'  -- Name - varchar(50)
             , 0  -- IsActive - bit
             ),
            ( ''  -- Name - varchar(50)
             , 0  -- IsActive - bit
             );


CREATE TABLE #ArticleGroupsTable
    (
     PartnerId INT
    ,Name VARCHAR(50)
    );

INSERT INTO [#ArticleGroupsTable]
        ( [PartnerId], [Name] )
    VALUES  ( 1, 'Test1' ),
            ( 2, 'Test2' ),
            ( 4, 'Test4' );

SELECT *
    FROM [#UpdCmmPartnerArticleGroup];
SELECT *
    FROM [#ArticleGroupsTable];
UPDATE [#UpdCmmPartnerArticleGroup]
    SET [IsActive] = 1
    ,   [Name] = (
                   SELECT t.[Name] AS GroupName
                    FROM [#ArticleGroupsTable] t
                    LEFT JOIN [#UpdCmmPartnerArticleGroup] s
                        ON [s].[PartnerId] = [t].[PartnerId]
                           AND [s].[Name] = [t].[Name]
                    WHERE [s].[PartnerId] IS NULL
                 )
    WHERE [#UpdCmmPartnerArticleGroup].[Name] = ''
        AND [IsActive] = 0;

SELECT *
    FROM [#UpdCmmPartnerArticleGroup];

SQL Server: Update, set a value that is within another select

4 Answers4