34,590
社区成员
发帖
与我相关
我的任务
分享
UPDATE xtm12 --
SET xtm12.xtysmc = LTRIM(RTRIM(xtm12.xtysmc))
+ LTRIM(RTRIM(CCC.xtpssm)) --
FROM dbo.dhm42 CCC
WHERE CCC.xtpzgg = xtm12.xtpzgg
AND ( LEN(LTRIM(RTRIM(xtm12.xtysmc)) + LTRIM(RTRIM(CCC.xtpssm))) < 45 )
AND ( CHARINDEX(LTRIM(RTRIM(CCC.xtpssm)), xtm12.xtysmc) = 0 )
AND xtm12.xtysmc IS NOT NULL
AND LTRIM(RTRIM(CCC.dhyydm)) = LTRIM(RTRIM(xtm12.wpsxlb2))
SELECT COUNT(*) errCnt
FROM (
SELECT #t1.id,
COUNT(*) cnt
FROM #t1
JOIN #t2
ON #t1.id = #t2.fid
GROUP BY #t1.id
HAVING COUNT(*) > 1
) T
DROP TABLE #t1
DROP TABLE #t2
CREATE TABLE #t1(id int, xtysmc varchar(10))
INSERT INTO #t1 VALUES(1,'A')
CREATE TABLE #t2(fid int, xtpssm varchar(10))
INSERT INTO #t2 VALUES(1,'B')
INSERT INTO #t2 VALUES(1,'C')
UPDATE #t1
SET #t1.xtysmc = #t1.xtysmc + #t2.xtpssm
FROM #t2
WHERE #t1.id = #t2.fid --其中满足这条件,如果#t2.xtpssm能返回行数大于2行,这本身就是有问题。
--oracle 会直接报错的。这也给数据带来很多不确定性。
SELECT * FROM #t1
DROP TABLE #t1
DROP TABLE #t2
CREATE TABLE #t1(id int, xtysmc varchar(10))
INSERT INTO #t1 VALUES(1,'A')
CREATE TABLE #t2(fid int, xtpssm varchar(10))
INSERT INTO #t2 VALUES(1,'B')
INSERT INTO #t2 VALUES(1,'C')
UPDATE #t1
SET #t1.xtysmc = #t1.xtysmc + #t2.xtpssm
FROM #t2
WHERE #t1.id = #t2.fid
SELECT * FROM #t1
id xtysmc
----------- ----------
1 AB
UPDATE xtm12 --
SET xtm12.xtysmc = LTRIM(RTRIM(xtm12.xtysmc))
+ ( SELECT LTRIM(RTRIM(CCC.xtpssm)) + '' --我的意思是说,如果CCC.xtpssm有多个值符合条件,那具体使用的是哪个就是不是随机了?
FROM dbo.dhm42 CCC
WHERE CCC.xtpzgg = xtm12.xtpzgg
AND ( LEN(LTRIM(RTRIM(xtm12.xtysmc))
+ LTRIM(RTRIM(CCC.xtpssm))) < 45 )
AND ( CHARINDEX(LTRIM(RTRIM(CCC.xtpssm)), xtm12.xtysmc) = 0 )
AND xtm12.xtysmc IS NOT NULL
AND LTRIM(RTRIM(CCC.dhyydm)) = LTRIM(RTRIM(xtm12.wpsxlb2))
FOR
XML PATH('')
)
WHERE EXISTS ( SELECT 1
FROM dbo.dhm42 DDD
WHERE DDD.xtpzgg = xtm12.xtpzgg
AND ( LEN(LTRIM(RTRIM(xtm12.xtysmc))
+ LTRIM(RTRIM(DDD.xtpssm))) < 45 )
AND ( CHARINDEX(LTRIM(RTRIM(DDD.xtpssm)), xtm12.xtysmc) = 0 )
AND xtm12.xtysmc IS NOT NULL
AND LTRIM(RTRIM(DDD.dhyydm)) = LTRIM(RTRIM(xtm12.wpsxlb2)) );
UPDATE xtm12 --
SET xtm12.xtysmc = LTRIM(RTRIM(xtm12.xtysmc))
+ LTRIM(RTRIM(CCC.xtpssm)) --我的意思是说,如果CCC.xtpssm有多个值符合条件,那具体使用的是哪个就是不是随机了?
FROM dbo.dhm42 CCC
WHERE CCC.xtpzgg = xtm12.xtpzgg
AND ( LEN(LTRIM(RTRIM(xtm12.xtysmc)) + LTRIM(RTRIM(CCC.xtpssm))) < 45 )
AND ( CHARINDEX(LTRIM(RTRIM(CCC.xtpssm)), xtm12.xtysmc) = 0 )
AND xtm12.xtysmc IS NOT NULL
AND LTRIM(RTRIM(CCC.dhyydm)) = LTRIM(RTRIM(xtm12.wpsxlb2))