一、int类型比如11在前边都加上301(变成30111),需要先把int转成字符串型添加301以后再转换成int类型
------------------------
UPDATE [tb].[dbo].[a]
SET code = convert(int,'301'+convert(nvarchar(50),code))
WHERE name='a'
二、A.c1与B.c1相等,用一条sql语句,实现A.c2的值更新为B.c3
------------------------
UPDATE A
SET A.c2 =B.c3
from A ,B
where A.c1=B.c1
或
UPDATE A
SET A.c2 =B.c3
from A inner join B on A.c1=B.c1
根据一个表日期中13号查出的其中一个字段更新到23号这个字段,同理只是把A,B用同一个表
------------------------
UPDATE A
SET A.c1 =B.c1
from [KQ] A inner join [KQ] B on A.[考勤号码]=B.[考勤号码]
where A.[date] ='2013-01-23' and B.[date]='2013-01-13' and A.company='北京'
Update中使用Case语句
UPDATE Personnel
SET salary = CASE WHEN salary >= 5000
THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600
THEN salary * 1.15
ELSE salary END
这里要注意一点,最后一行的ELSE salary是必需的,要是没有这行,不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。
一张表求出的和,更新到另一张表(聚合函数查询的结果,作为B表更新A表中的数据)
update A
SET A.Score = B.sum1
FROM A,(SELECT [name]
,[code]
,[name]
,sum(convert(int,[value]) ) as sum1
FROM [Score]
where [name]='条件'
group by [name]
,[code],[name]) B
WHERE A.code = B.[code] and A.code in('12345678')