两张表wl_purorderh和gg_itemcode,要将wl_purorderh表里的unitid替换为gg_itemcode的unitid,替换条件是
gg_itemcode.itemcode=wl_purorderh.itemcode
我写的如下:
update wl_purorderh
set wl_purorderh.unitid=gg_itemcode.unitid
from wl_purorderh,gg_itemcode
where gg_itemcode.itemcode=wl_purorderh.itemcode今天被别人笑了,看看大家还有别的什么写法比较好。能更漂亮一点!!
gg_itemcode.itemcode=wl_purorderh.itemcode
我写的如下:
update wl_purorderh
set wl_purorderh.unitid=gg_itemcode.unitid
from wl_purorderh,gg_itemcode
where gg_itemcode.itemcode=wl_purorderh.itemcode今天被别人笑了,看看大家还有别的什么写法比较好。能更漂亮一点!!
set
unitid=g.unitid
from
gg_itemcode g
where
w.itemcode=g.itemcode
set
unitid=g.unitid
from
gg_itemcode g
where
wl_purorderh.itemcode=g.itemcode
set
unitid=g.unitid
from
gg_itemcode g
where
w.itemcode=g.itemcode
set
unitid=g.unitid
from
gg_itemcode g
where
w.itemcode=g.itemcode
注意,这样的句法是错误的,要用别名要在From 后加才可以
另外,有很多系统存储过程,你可以参考他们的写法
如:想看系统存储过程SP_LOCK是怎么写的,可以在查询分析器里如下写:
SP_HELPTEXT SP_LOCK
---------------------------------
把结果集复制粘贴就可以看了
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as
set nocount on
/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and req_spid in (@spid1, @spid2)
end
/*
** No parameters, so show all the locks.
*/
else
begin
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
order by spid
end
return (0) -- sp_lock
update WL
set WL.unitid=GG.unitid
from wl_purorderh WL,gg_itemcode GG
where GG.itemcode=WL.itemcode不过在写编码的时,只要有自己的的风格也行。自己固定的格式。
update wl_purorderh w (这个W查询分析的时候老报错)
set
unitid=g.unitid
from
gg_itemcode g
where
w.itemcode=g.itemcode
感觉子陌红尘这个好象有错的!