Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

need to update table @groups

where i must put the update sequence
update @groups set KK= RESULT FROM THE CONSUMPTION

DECLARE @Groups TABLE ( GroupID varchar(12) ,KK varchar(100));

INSERT  INTO @Groups
VALUES  ( 2591,'' );

DECLARE @Products TABLE
    (
      ProductID NVARCHAR(255)
    );

INSERT  INTO @Products
VALUES  ( '2591-16' ),
        ( '2591-17' ),
        ( '2591-18' );

WITH    Normalized
          AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                        PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
               FROM     @Products P
               WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                          FROM      @Groups G )
             )
    SELECT  O.GroupID ,
            ( SELECT    '@-' + I.SizeID
              FROM      Normalized I
              WHERE     I.GroupID = O.GroupID
			  FOR XML PATH('')
            ) AS Sizes
    FROM    Normalized O
    GROUP BY O.GroupID;

Open in new window

DECLARE groupid CURSOR FOR select groupid from #Product_Pool_2 
OPEN groupid
FETCH NEXT FROM groupid into @groupid
WHILE @@FETCH_STATUS = 0
BEGIN
print N'Groupid'+ @groupid;

WITH    Normalized
          AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                        PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
               FROM     dbo.product_Prices P
               WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                          FROM      #Product_Pool_2 G )
             )
    SELECT  
            ( SELECT    '/' + I.SizeID
              FROM      Normalized I
              WHERE     I.GroupID = O.GroupID
                    FOR XML PATH('')
            ) AS Sizes
    FROM    Normalized O
    GROUP BY O.GroupID;


FETCH NEXT FROM groupid into @groupid
END

CLOSE groupid
DEALLOCATE groupid

Open in new window

DECLARE groupid CURSOR FOR select groupid from #Product_Pool_2 
OPEN groupid
FETCH NEXT FROM groupid into @groupid
WHILE @@FETCH_STATUS = 0
BEGIN
print N'Groupid'+ @groupid;

WITH    Normalized
          AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                        PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
               FROM     dbo.product_Prices P
               WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                          FROM      #Product_Pool_2 G )
             )
    SELECT  
            ( SELECT    '/' + I.SizeID
              FROM      Normalized I
              WHERE     I.GroupID = O.GroupID
                    FOR XML PATH('')
            ) AS Sizes
    FROM    Normalized O
    GROUP BY O.GroupID;


FETCH NEXT FROM groupid into @groupid
END

CLOSE groupid
DEALLOCATE groupid

Open in new window

DECLARE groupid CURSOR FOR select groupid from #Product_Pool_2 
OPEN groupid
FETCH NEXT FROM groupid into @groupid
WHILE @@FETCH_STATUS = 0
BEGIN
print N'Groupid'+ @groupid;

WITH    Normalized
          AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                        PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
               FROM     dbo.product_Prices P
               WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                          FROM      #Product_Pool_2 G )
             )
    SELECT  
            ( SELECT    '/' + I.SizeID
              FROM      Normalized I
              WHERE     I.GroupID = O.GroupID
                    FOR XML PATH('')
            ) AS Sizes
    FROM    Normalized O
    GROUP BY O.GroupID;


FETCH NEXT FROM groupid into @groupid
END

CLOSE groupid
DEALLOCATE groupid

Open in new window

0
ebertsys
Asked:
ebertsys
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Couldn't find any reference to update @groups in your code.
0
 
ste5anSenior DeveloperCommented:
Please complete your example:

-- Table DDL.
DECLARE @Product_Pool_2 TABLE ( GroupID INT );
DECLARE @product_Prices TABLE ( ProductID INT );

-- Insert your test date here.
---

-- Your Logic.
DECLARE groupid CURSOR
FOR
    SELECT  GroupID
    FROM    @Product_Pool_2;
DECLARE @groupid INT;

OPEN groupid;
FETCH NEXT FROM groupid INTO @groupid;
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT N'Groupid' + @groupid;

        WITH    Normalized
                  AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                                PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
                       FROM     @product_Prices P
                       WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                                  FROM      @Product_Pool_2 G )
                     )
            SELECT  ( SELECT    '/' + I.SizeID
                      FROM      Normalized I
                      WHERE     I.GroupID = O.GroupID
                    FOR
                      XML PATH('')
                    ) AS Sizes
            FROM    Normalized O
            GROUP BY O.GroupID;


        FETCH NEXT FROM groupid INTO @groupid;
    END;

CLOSE groupid;
DEALLOCATE groupid;

-- Result: Please post the desired output of those two statements for the above sample data.
SELECT  *
FROM    @Product_Pool_2 PP;

SELECT  *
FROM    @product_Prices PP;

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You'll need to do a much better job defining what you're asking here.
Specifically, define ' RESULT FROM THE CONSUMPTION', and in the above four code blocks the bottom three appear to be identical.
0
 
ste5anSenior DeveloperCommented:
A CTE can be also used to update a column:

WITH    Normalized
                  AS ( SELECT   PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) AS GroupID ,
                                PARSENAME(REPLACE(P.ProductID, '-', '.'), 1) AS SizeID
                       FROM     @product_Prices P
                       WHERE    PARSENAME(REPLACE(P.ProductID, '-', '.'), 2) IN ( SELECT    G.GroupID
                                                                                  FROM      @Product_Pool_2 G )
                     ),
SizeInfo AS (
            SELECT  ( SELECT    '/' + I.SizeID
                      FROM      Normalized I
                      WHERE     I.GroupID = O.GroupID
                    FOR
                      XML PATH('')
                    ) AS Sizes,
O.GroupID
            FROM    Normalized O
            GROUP BY O.GroupID)
UPDATE G
SET KK = SI.Size 
FROM @groups G 
INNER JOIN SizeInfo SI ON G.GroupID = SI.GroupID;

Open in new window

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now