TSQL update 简单应用小总结
2013-10-22 23:59 c#在路上 阅读(669) 评论(0) 编辑 收藏 举报UPDATE 有两种基本的格式。一种是用静态数据来修改表,另一种是用其他表中的数据来修改表。下面是第一种格式:
1 UPDATE #famousjaycees 2 SET jc = 'Jhony cash', 3 occupation = 'singer/songwrite', 4 becamefamous = 1955, 5 notes = 'began c' 6 WHERE jc = 'Jhony caeer'
第二种格式如下:
1 IF NOT EXISTS 2 (SELECT * 3 FROM sys.sysobjects 4 WHERE [name] = 'semifamousjaycees') 5 CREATE TABLE semifamousjaycees 6 ( 7 jc VARCHAR (15), 8 occupation VARCHAR (25), 9 becamefamous INT DEFAULT 0, 10 notes TEXT NULL 11 ) 12 13 UPDATE f 14 SET jc = s.jc, 15 occupations = s.occupations, 16 becamefamous = s.becamefamous, 17 notes = s.notes 18 FROM famousejaycees f 19 JOIN semifamousjaycee ON (f.becamesou = s.becamefouse)
UPDATE 的另外一种用途:使用update 检测约束。
如果使用 bulk insert 或者其他大批量的载入工具来对有insett 触发器的表进行追加数据,你们你会发现触发器不能触发。而且,即使bulk insert 不妨碍约束,也会是操作变的非常的慢。如果在载入数据时忽略约束,那么就快的多了。所以,比较的好的方法是,载入数据结束后,马上在对表一个假的update 操作。这个假的修改操作只是简单地将列值为其身的值。这样就会触发触发器约束进行检测。如果其中有包含错数数据的行,那么update失败.例如:
1 IF NOT EXISTS 2 (SELECT * 3 FROM sys.sysobjects 4 WHERE [name] = 'famousejaycees') 5 CREATE TABLE famousejaycees 6 ( 7 jc VARCHAR (15) CHECK (left (jc, 3) <> 'Joe'), -- establish a check constraint 8 occupation VARCHAR (25), 9 becamefamous INT DEFAULT 0, 10 notes TEXT NULL 11 ) 12 go 13 14 BULK INSERT famousejaycees 15 FROM 'C:\famous.bcp' 16 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\r\n') 17 18 --check that the miscreant is in place 19 SELECT * FROM famousejaycees 20 21 --now do the faux update 22 23 update famousejaycees 24 set jc=jc, occupation = occupation, becamefamous = becamefamous, notes = notes
使用update 交换行列:
IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE [name] = 'sample') CREATE TABLE sample ( k1 INT IDENTITY, samp1 FLOAT DEFAULT (rand () * 1000), samp2 FLOAT DEFAULT (rand () * 1000) ) SELECT * FROM sample; DECLARE @swap FLOAT UPDATE sample SET @swap = samp1, samp1 = samp2, samp2 = @swap