IF OBJECT_ID('Ta') IS NOT NULL DROP TABLE Ta GOCREATE TABLE Ta ( ID INT, [EXP] INT );INSERT Ta SELECT 11,123 UNION SELECT 12,1234SELECT * FROM Ta A WHERE EXISTS ( SELECT 1 FROM Ta B WHERE A.ID=B.ID AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0 )/* ID EX ----------------- 12 1234(1 行受影响) */
不是的是只删除12这个数是删除12这一行吧,这样就可以了。 IF OBJECT_ID('Ta') IS NOT NULL DROP TABLE Ta GOCREATE TABLE Ta ( ID INT, [EXP] INT );INSERT Ta SELECT 11,123 UNION SELECT 12,1234DELETE A FROM Ta A WHERE EXISTS ( SELECT 1 FROM Ta B WHERE A.ID=B.ID AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0 )
不是的是只删除12这个数是删除12这一行吧,这样就可以了。 IF OBJECT_ID('Ta') IS NOT NULL DROP TABLE Ta GOCREATE TABLE Ta ( ID INT, [EXP] INT );INSERT Ta SELECT 11,123 UNION SELECT 12,1234DELETE A FROM Ta A WHERE EXISTS ( SELECT 1 FROM Ta B WHERE A.ID=B.ID AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0 ) 这样吧我用白话表述了,我们判断ID列为N 中的exp数值ABCDEFG是否包含ABC,如果有就删除ABC,写入结果DEFG,这样够明白了吧
不是的是只删除12这个数是删除12这一行吧,这样就可以了。 IF OBJECT_ID('Ta') IS NOT NULL DROP TABLE Ta GOCREATE TABLE Ta ( ID INT, [EXP] INT );INSERT Ta SELECT 11,123 UNION SELECT 12,1234DELETE A FROM Ta A WHERE EXISTS ( SELECT 1 FROM Ta B WHERE A.ID=B.ID AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0 ) 这样吧我用白话表述了,我们判断ID列为N 中的exp数值ABCDEFG是否包含ABC,如果有就删除ABC,写入结果DEFG,这样够明白了吧 IF OBJECT_ID('Ta') IS NOT NULL DROP TABLE Ta GO CREATE TABLE Ta ( ID INT, [EXP] VARCHAR(20) ); INSERT Ta SELECT 11,'123' UNION SELECT 12,'1234' DECLARE @ID INT --指定ID列的值 DECLARE @S VARCHAR(20) --要查找并替换的值 SET @ID=11 SET @S='12' UPDATE a Set a.[EXP]=STUFF(a.[EXP],Charindex(@S,a.[EXP]),len(@S),'') FROM ( SELECT ID,[EXP] FROM Ta a WHERE a.ID=@ID AND CHARINDEX(@S,CONVERT(VARCHAR(20),a.[EXP]))<>0 ) a WHERE a.ID=@ID select * from ta /* ID EXP --------------- 11 3 12 1234 */
if exists(select * from [datebase] where ID=11 and [exp] like '%12%') update [datebase] set EXP = (select replace(exp,'12','') as value from datebase where ID=11 and EXP like '%12%') where ID=11 and EXP like '%12%' else print '没有这个数值' 这个更加的简单了还是感谢你
是不是这个意思,EXP列的值如果和当前行的ID列的值相同,就删除?
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GOCREATE TABLE Ta
(
ID INT,
[EXP] INT
);INSERT Ta
SELECT 11,123 UNION
SELECT 12,1234SELECT * FROM Ta A
WHERE EXISTS (
SELECT 1
FROM Ta B
WHERE A.ID=B.ID
AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0
)/*
ID EX
-----------------
12 1234(1 行受影响)
*/
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GOCREATE TABLE Ta
(
ID INT,
[EXP] INT
);INSERT Ta
SELECT 11,123 UNION
SELECT 12,1234DELETE A FROM Ta A
WHERE EXISTS (
SELECT 1
FROM Ta B
WHERE A.ID=B.ID
AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0
)
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GOCREATE TABLE Ta
(
ID INT,
[EXP] INT
);INSERT Ta
SELECT 11,123 UNION
SELECT 12,1234DELETE A FROM Ta A
WHERE EXISTS (
SELECT 1
FROM Ta B
WHERE A.ID=B.ID
AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0
)
这样吧我用白话表述了,我们判断ID列为N 中的exp数值ABCDEFG是否包含ABC,如果有就删除ABC,写入结果DEFG,这样够明白了吧
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GOCREATE TABLE Ta
(
ID INT,
[EXP] INT
);INSERT Ta
SELECT 11,123 UNION
SELECT 12,1234DELETE A FROM Ta A
WHERE EXISTS (
SELECT 1
FROM Ta B
WHERE A.ID=B.ID
AND CHARINDEX(CONVERT(VARCHAR(10),A.ID),CONVERT(VARCHAR(10),B.[EXP]))<>0
)
这样吧我用白话表述了,我们判断ID列为N 中的exp数值ABCDEFG是否包含ABC,如果有就删除ABC,写入结果DEFG,这样够明白了吧
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta
(
ID INT,
[EXP] VARCHAR(20)
);
INSERT Ta
SELECT 11,'123' UNION
SELECT 12,'1234' DECLARE @ID INT --指定ID列的值
DECLARE @S VARCHAR(20) --要查找并替换的值
SET @ID=11
SET @S='12'
UPDATE a Set a.[EXP]=STUFF(a.[EXP],Charindex(@S,a.[EXP]),len(@S),'')
FROM
(
SELECT ID,[EXP]
FROM Ta a
WHERE a.ID=@ID
AND CHARINDEX(@S,CONVERT(VARCHAR(20),a.[EXP]))<>0
) a
WHERE a.ID=@ID select * from ta
/*
ID EXP
---------------
11 3
12 1234
*/
update [datebase] set EXP = (select replace(exp,'12','') as value from datebase where ID=11 and EXP like '%12%') where ID=11 and EXP like '%12%'
else
print '没有这个数值'
这个更加的简单了还是感谢你