SQL随笔

--IN 操作符允许我们在 WHERE 子句中规定多个值。
UPDATE aaa1 SET
aaa1.name = '333',
aaa1.message = '333'
WHERE
aaa1.id IN (2,3)

 

--备份测试表建立
select * into CRM_Objectbeifen from (
select * from CRM_Object
) CRM_Objectbeifen

 

--把表中NULL修改为''
UPDATE [Table1] SET Name='' WHERE Name IS NULL

 

--随笔
UPDATE a SET 
a.name = b.name
FROM 
tab1 a
LEFT JOIN 
tab2 b 
ON 
a.oid = b.oid
WHERE b.oid IS NOT NULL

INSERT INTO Affter_ProjectProgressRelate ([OID],[Name])
(SELECT * FROM [aaa0204xmjz] WHERE OID NOT IN 
(SELECT OID FROM Affter_ProjectProgressRelate)
)

 

--查重
SELECT OID,COUNT(*) FROM dbo.Table
GROUP BY OID
ORDER BY COUNT(*) DESC

 

--删除查出Class为空的行
DELETE FROM Tab1 WHERE OID IS
(SELECT OID FROM Tab1 WHERE Class IS NULL)

 

--INSERT 语句中行值表达式的数目超出了 1000 行值的最大允许值
INSERT INTO TABLE1 
(name,sex) 
SELECT t.name,t.sex 
FROM (VALUES 
('1','男'),('2','男'),('3','男'),('4','男'),('5','男'),('6','男'),('1001','男')) 
AS t(name,sex)

 

--SQL两表连查修改
update 表1
set
表1.class_id = b.class_id 
from
表1 AS a,
表2 AS b
where 
a.OID = b.OID

--注意第三行
--表1.class_id = b.class_id 
--要修改的表必须用表名引出来
--[表1.class_id] 
--不能
--a.class_id