----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-01-09 09:13:55
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[test1]
if object_id('[test1]') is not null drop table [test1]
go 
create table [test1]([Max_Bill_ID] int,[Max_List_ID] int)
insert [test1]
select 5,10
--> 测试数据：[test2]
if object_id('[test2]') is not null drop table [test2]
go 
create table [test2]([Bill_ID] int,[List_ID] int)
insert [test2]
select 86,1606 union all
select 86,1608 union all
select 87,1618
--> 测试数据：[test3]
if object_id('[test3]') is not null drop table [test3]
go 
create table [test3]([Bill_ID] int,[List_ID] int,[Color_ID] int,[Size_ID] int,[Prod_Number] numeric(9,8))
insert [test3]
select 86,1606,828,833,1.00000000 union all
select 86,1606,828,834,2.00000000 union all
select 86,1606,828,835,3.00000000 union all
select 86,1608,828,833,1.00000000 union all
select 86,1608,828,834,2.00000000 union all
select 86,1608,828,835,3.00000000 union all
select 87,1618,828,833,1.00000000 union all
select 87,1618,828,834,2.00000000 union all
select 87,1618,828,835,3.00000000
--------------开始查询--------------------------
update 
   c
set
   Bill_ID=a.[Max_Bill_ID] 
from
   test1 a,test3 c


update 
   b
set
   Bill_ID=a.[Max_Bill_ID],List_ID=a.Max_List_ID+b.px
from
   test1 a,
   (select row_number()over(order by getdate()) as px,* from test2)b
   


   
select * from test3 
select * from test2
----------------结果----------------------------
/* 
(1 行受影响)

(3 行受影响)

(9 行受影响)

(9 行受影响)

(3 行受影响)
Bill_ID     List_ID     Color_ID    Size_ID     Prod_Number
----------- ----------- ----------- ----------- ---------------------------------------
5           1606        828         833         1.00000000
5           1606        828         834         2.00000000
5           1606        828         835         3.00000000
5           1608        828         833         1.00000000
5           1608        828         834         2.00000000
5           1608        828         835         3.00000000
5           1618        828         833         1.00000000
5           1618        828         834         2.00000000
5           1618        828         835         3.00000000

(9 行受影响)

Bill_ID     List_ID
----------- -----------
5           11
5           1