现在在用sqlserver和mysql,两个数据库的语法还不完全一样,用的时候还得百度,麻烦,遂记录一下:
sqlserver:
多表update:
update a set a.ssglid=b.id from xs_ssgl a,jc_ssgl b where a.ssglid=0 and a.xq=b.xq
存储过程:
变量赋值:
select top 1 @ssglId=id,@xq=xq from jc_ssgl
判断:
if @pSfyjsbf = '' or @sfyjsbf = @pSfyjsbf
begin
set @i = 1;
end
else
begin
set @i = 1;
end
游标:
DECLARE cursor_jhk CURSOR FOR select id,xbdm from xs_ssgl where bj is not null and bj != '' and nf = @pNf;
OPEN cursor_jhk
FETCH NEXT FROM cursor_jhk INTO @id,@xbdm
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cursor_jhk INTO @id,@xbdm
END
CLOSE cursor_jhk
DEALLOCATE cursor_jhk
踩过的坑:
-- 外层会有游标循环
set @ssglId=null;-- 此处不加初始化,下面查询条件中的如果查不到,那么@ssglId将使用上一次的数据,很奇怪sqlserver为什么没有把空值赋给它,坑了近一个小时
select top 1 @ssglId=id,@xq=xq from jc_ssgl where ID not in (
select ssglId from xs_ssgl where nf=@pNf);
if @ssglId is not null
begin
-- 更新
update xs_ssgl set ssglid=@ssglId,xq=@xq where id=@id;
end
mysql:
多表update:
UPDATE aaa a, departments b SET d=b.id WHERE a.c=b.department