update B set xs_price=(select max(price) from A where sp_id=b.sp_id)
declare @a table(sp_id varchar(10),price int) insert @a select '00001',10 union all select 00002,15 union all select 00002,16 union all select 00003,6 union all select 00003,5declare @b table (xs_id varchar(10),sp_id varchar(10),xs_price int) insert @b select 'xs001',00002,23 union all select 'xs002',00003,4 union all select 'xs003', 00002,22--语句: update b set xs_price=(select max(price) from @a where sp_id=b.sp_id) from @b b结果: xs_id sp_id xs_price ---------- ---------- ----------- xs001 2 16 xs002 3 6 xs003 2 16(所影响的行数为 3 行) select * from @b
insert @a select '00001',10
union all select 00002,15
union all select 00002,16
union all select 00003,6
union all select 00003,5declare @b table (xs_id varchar(10),sp_id varchar(10),xs_price int)
insert @b select 'xs001',00002,23
union all select 'xs002',00003,4
union all select 'xs003', 00002,22--语句:
update b set xs_price=(select max(price) from @a where sp_id=b.sp_id) from @b b结果:
xs_id sp_id xs_price
---------- ---------- -----------
xs001 2 16
xs002 3 6
xs003 2 16(所影响的行数为 3 行)
select * from @b