当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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]

)

网友答案:

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
网友答案:
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)
分享给朋友:
您可能感兴趣的文章:
随机阅读: