UPDATE查詢結果範圍內的資料

hky87發表於2013-09-23
語法
http://technet.microsoft.com/zh-cn/library/ms177523.aspx
Transact-SQL
[ WITH  [...n] ]
UPDATE 
    [ TOP ( expression ) [ PERCENT ] ] 
    { { table_alias |  | rowset_function_limited 
         [ WITH (  [ ...n ] ) ]
      }
      | @table_variable    
    }
    SET
        { column_name = { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name = expression
                                | field_name = expression }
                                | method_name ( argument [ ,...n ] )
                              }
          }
          | column_name { .WRITE ( expression , @Offset , @Length ) }
          | @variable = expression
          | @variable = column = expression
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...n ] 

    [  ]
    [ FROM{  } [ ,...n ] ] 
    [ WHERE {  
            | { [ CURRENT OF 
                  { { [ GLOBAL ] cursor_name } 
                      | cursor_variable_name 
                  } 
                ]
              }
            } 
    ] 
    [ OPTION (  [ ,...n ] ) ]
[ ; ]

 ::=
{ 
    [ server_name . database_name . schema_name . 
    | database_name .[ schema_name ] . 
    | schema_name .
    ]
    table_or_view_name}

範例
with a as
((select u.empno,i.entrydate,u.pwd
  from PteeInfo i,dbo.User u,UserRole ur
   where i.empno=u.empno and u.empno=ur.empno and ur.role_name='PPP'
and ProJobID in (12,13)

))
update a
set pwd = ‘xxxx‘

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10314474/viewspace-773112/，如需轉載，請註明出處，否則將追究法律責任。

相關文章