CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
JoeFletch
JoeFletch

Reputation: 3970

SQL Update Parent Table Field based on Multiple Child Table Rows with Length Requirement from Each Child Row

I have the following sample table structures for a parent / child relationship.

parent table

id    ShortText
--------------
10    
20

child table

id    char     value    order
-----------------------------
10    COLOR    RED      1
10    HEIGHT   1FT      2
10    LENGTH   10FT     5
20    COLOR    BLUE     1
20    WEIGHT   100LBS   3
20    WIDTH    2FT      4

I have a requirement that specifies that certain fields from the child table rows need to be concatenated together and placed in the parent table ShortText field. The ShortText field can be a maximum of 10 characters.

I would expect (or I should say I WANT) to get this.

id    ShortText
---------------
10    RED,1FT,
20    BLUE,2FT,

I would like to complete this in a single SQL query if possible. Any suggestions as to how I can do this and avoid iterating through each child record set? Any help is appreciated!

Here's what I have tried so far.

The following update statement...

UPDATE
  parent
SET
  ShortText = COALESCE(ShortText, N'') + 
           CASE WHEN DATALENGTH(COALESCE(ShortText, N'') + child.val + ',')/2 < 10
                THEN COALESCE(ShortText, N'') + child.val + ',' ELSE N'' END
FROM
  parent INNER JOIN child
    ON
      parent.id = child.id

But I only get the following results.

id    ShortText
------------------
10    RED,
20    BLUE,

I also tried the following and was unsuccessful because the case statement that is checking the length is not executed on each child table row, only on the before ShortText.

UPDATE
    p
SET
    p.ShortText = p.ShortText +
        STUFF((
        SELECT
            CASE WHEN 
                    DATALENGTH(p.ShortText)/2 + DATALENGTH(c.val + N',')/2 < 10
                THEN
                    c.val + N','
                ELSE
                    N''
                END
        FROM
            child as c
        WHERE
            c.id = p.id
        FOR XML PATH(''), type).value('.','NVARCHAR(max)'), 1, 0, N'')
FROM
    parent as p

Here is a SQLfiddle for reference. I also posted and got an answer for a similar question here, but this is a different requirement and I can not scale it for the length requirement. As always, any help is much appreciated.

Upvotes: 2

Views: 3339

Answers (1)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Check p.ShortText on NULL and then update derived table

UPDATE x
SET x.ShortText = NewShortText
FROM (SELECT p.ShortText, STUFF((SELECT CASE WHEN 
                          DATALENGTH(ISNULL(p.ShortText, ''))/2 +  DATALENGTH(ISNULL(c.value, '') + N',')/2 < 10
                                             THEN c.value + N','
                                             ELSE N'' END
FROM child as c
WHERE c.id = p.id
FOR XML PATH(''), type).value('.','NVARCHAR(max)'), 1, 0, N'') AS NewShortText 
FROM parent as p) x

UPDATE 14.11.2012

;WITH cte (id, uid, val)
AS
 (
  SELECT ROW_NUMBER() OVER(PARTITION BY p.[uid] ORDER BY c.val DESC),
         p.uid, c.val
  FROM parent p INNER JOIN child c ON p.uid = c.uid
  ), cte2 (id, uid, val)
AS
 (  
  SELECT id, uid, val
  FROM cte
  WHERE id = 1 
  UNION ALL
  SELECT c.id, c.uid, CAST(ISNULL(ct.val, '') + ', ' + ISNULL(c.val, '') AS nvarchar(100)) AS val
  FROM cte c JOIN cte2 ct ON c.id = ct.id + 1 AND c.uid = ct.uid
)
UPDATE x
SET x.ShortText = x.MaxNewShortText
FROM
 (
  SELECT p.uid, p.ShortText, CASE WHEN DATALENGTH(val)/2 < 10
                                  THEN val END AS NewShortText,
  MAX(CASE WHEN DATALENGTH(val)/2 < 10
           THEN val END)
  OVER(PARTITION BY p.[uid]) AS MaxNewShortText
  FROM parent p JOIN cte2 ct ON p.uid = ct.uid
  ) x

Demo on SQLFiddle

Upvotes: 2

Related Questions