CODEWITHSUNDEEP
sqlt-sql
GregWW
GregWW

Reputation: 127

How to update field to longest string of multiple tables/rows?

I'm trying to merge the "best" data from two tables into the first table, but I can't figure out how to select the best data inside the set statement.

In this case, "best" data means "longest". Table1 has unique IDs, but Table2 has duplicates of the ID. The information is essentially the same, but I want the fields with the longest strings from Table1 or Table2, possibly not all from the same row.

I tried this first, but of course, merge fails when there are two matching rows in Table2:

USE DatabaseName
MERGE Table1 AS a
USING Table2 AS b1
ON a.[ID-unique] LIKE b1.[ID-not-unique]
WHEN MATCHED THEN UPDATE
SET a.[Field1] =
    CASE 
        WHEN a.[Field1] IS NULL
            THEN b1.[Field1]
        WHEN LEN(a.[Field1]) < LEN(b1.[Field1])
            THEN b1.[Field1]
        ELSE a.[Field1] END,
a.[Field2] =
    CASE
        WHEN a.[Field2] IS NULL
            THEN b1.[Field2]
        WHEN LEN(a.[Field2]) < LEN(b1.[Field2])
            THEN b1.[Field2]
        ELSE a.[Field2] END;

I used something like this elsewhere to select the longest string, but I get errors when I try to include it as the "THEN" statement:

SELECT [Field] FROM Table2 b2
WHERE 
        LEN([Field])
        =   
        (
        SELECT MAX(LEN([Field]))
        FROM [Table2] AS b3
        WHERE b2.[ID-not-unique] = b3.[ID-not-unique]
        )

Upvotes: 2

Views: 60

Answers (2)

paparazzo
paparazzo

Reputation: 45096

UPDATE T1
   SET T1.Field = T2.Field 
  FROM Table1 T1
  JOIN Table2 T2
    ON T2.[ID-not-unique] = T1.[ID-unique]
   AND len(T1.Field) < len(T2.Field)

Upvotes: 0

Lamak
Lamak

Reputation: 70668

Try this:

UPDATE T1
SET T1.Field = CASE 
                    WHEN LEN(T1.Field) < LEN(T2.Field) THEN T2.Field 
                    ELSE T1.Field 
                END
FROM Table1 T1
OUTER APPLY (SELECT TOP 1 *
             FROM Table2
             WHERE [ID-not-unique] = T1.[ID-unique]
             ORDER BY LEN([Field]) DESC) T2

Upvotes: 1

Related Questions