高分，高手请进，用一张表更新另一张表，急！！！

[zhang_bamboo]

示例：在SQL server2000中如何用一个表更新另一个表的sql
table1(field1,field2)

t2able(field1,field2)

根据相同的f1,用table2.field2更新table1.field2

update table1 set field2 = b.field2 from table1 a,table2 b where a.field1 = b.field1

现有表：
1、菜品表：[dbo].[Dishes](
[DishesID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY ,
[DishesCode] [varchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[DishesName] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[DishesPicture] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[DishesMove] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[DishesDesc] [text] COLLATE Chinese_PRC_CI_AS NULL,
[DishesState] [smallint] NULL,
[DishesVersion] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[GeneralMode] [int] NULL,
[FamilyMode] [int] NULL,
[BusinessMode] [int] NULL,
[MeetingMode] [int] NULL）

2、菜品区域表：[dbo].[AreaDishes](
[AreaID] [bigint] NOT NULL,
[DishesID] [bigint] NOT NULL,
[Unit] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Price] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Version] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,
[State] [smallint] NULL）

6、dbo.AreaDishes20101015002 菜品区域表：[dbo].[AreaDishes]的备份表


3、dbo.[菌说炒菜调价1]表（导入）：[dbo].[菌说炒菜调价1](
[品名] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[进价] [float] NULL,
[出成率] [float] NULL,
[成本价] [float] NULL,
[装盘量] [float] NULL,
[调整后售价] [int] NULL,
[毛利率] [float] NULL,
[目前售价] [int] NULL,
[调整差异] [float] NULL
)
4、菌说炒菜调价$表（导入）：[dbo].[菌说炒菜调价$](
[品名] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[主料进价] [float] NULL,
[主料出成率] [float] NULL,
[辅料进价] [float] NULL,
[辅料出成率] [float] NULL,
[主料成本价] [float] NULL,
[辅料成本价] [float] NULL,
[调料] [float] NULL,
[主料出品份量] [float] NULL,
[辅料出品份量] [float] NULL,
[总成本] [float] NULL,
[调整后售价] [int] NULL,
[毛利率] [float] NULL,
[目前售价] [int] NULL,
[调整差异] [float] NULL,
[F16] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[F17] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[F18] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[F19] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
)

要达到效果：根据两张导入表，更新现有的菜品区域表。但是导入表中只有菜品名，没有菜品编号 所以要关联菜品表

我的Sql语句：
update dbo.AreaDishes20101015002 set Price= '￥'+convert(varchar(5),nb.prc)
from (select d.DishesName,d.DishesVersion,ad.Price from dbo.AreaDishes as ad
inner join dbo.Dishes as d on ad.DishesID=d.DishesID
where ad.Price<>'时价' and ad.AreaID in(23,24)) as a,
(select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价1] union all
select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价$]) as nb
where a.DishesName = nb.dn and nb.nprc=convert(int,replace(a.Price,'￥',''))
and a.Price<>'时价' and AreaID in(23,24)

update table1 set field2 = b.field2 from table1 a,table2 b where a.field1 = b.field1


select *,'￥'+convert(varchar(5),nb.prc) from
(select d.DishesName,d.DishesVersion,ad.Price,ad.AreaID from dbo.AreaDishes as ad
inner join dbo.Dishes as d on ad.DishesID=d.DishesID) a inner join
(select 品名 as dn,调整后售价 as prc,目前售价 as oprc from dbo.[菌说炒菜调价1] union all
select 品名 as dn,调整后售价 as prc,目前售价 as oprc from dbo.[菌说炒菜调价$]) nb
on a.DishesName = nb.dn and nb.oprc=convert(int,replace(a.Price,'￥',''))
where a.Price<>'时价' and a.AreaID in(23,24)

在使用第二句查询时，只有32条数据，使用第一条跟新就变成几千条了，折腾了好久，实在没招，高手，帮帮忙吧
（我想给200分呢，不知道为什么只能给100分，应该是分不过吧）

[bancxc]

sff[Quote=引用 1 楼 billpu 的回复:]
太复杂了 帮顶
[/Quote]

[shibinysy]

首先说明,Update是不会增加记录条数.
不过你上面的sql有点长.不能够很快的看出哪里出问题了.你需要自己逐个揣摩推测.

[zhang_bamboo]

刚刚因为急着下班，所以随便贴出来就没仔细做说明：
菜品表：[dbo].[Dishes]：主要记录菜品的基本信息，重点要用的字段是：
[DishesName] [varchar](100) 菜品名称

菜品区域表：[dbo].[AreaDishes]：主要记录的是每个菜品在不同区域下的价格，重点要用的字段是：
[AreaID] [bigint] 区域ID
[Price] [varchar](20) 价格

导入的两张表虽然看着很复杂（主要记录一些菜的成本，原价，现在要修改的价格，及毛利率），字段也不太一样，但是，重点字段很少就是
[品名] 即 菜品名称
[调整后售价]
[目前售价]

现在要达到的目的就是：
根据导入表中的菜品名称和原来售价，将区域表中的价格改为 [调整后售价]
这样够清楚了吧，也不算复杂啊，我只是不怎么用 一张表更新另一张表的，所以不知道逻辑错在说明地方，还请各位帮帮忙

[dawugui]

好长,先洗澡再来看.

[SQLCenter]

-- 查询
select
	*
from 
	AreaDishes20101015002 a
inner join
	Dishes b
	on a.DishesID=b.DishesID
inner join
(
	select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价1] union
	select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价$]
) c
	on b.DishesName=c.dn and c.nprc=convert(int,replace(a.Price,'￥',''))
where
	a.Price<>'时价' and a.AreaID in(23,24)

-- 更新
update a set
	a.Price= '￥'+convert(varchar(5),c.prc)
from 
	AreaDishes20101015002 a
inner join
	Dishes b
	on a.DishesID=b.DishesID
inner join
(
	select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价1] union
	select 品名 as dn,调整后售价 as prc,目前售价 as nprc from dbo.[菌说炒菜调价$]
) c
	on b.DishesName=c.dn and c.nprc=convert(int,replace(a.Price,'￥',''))
where
	a.Price<>'时价' and a.AreaID in(23,24)

[SQLCenter]

更新 菜品区域表：[dbo].[AreaDishes] 则替换 AreaDishes20101015002 为 AreaDishes

[zhang_bamboo]

楼上那位，非常感谢，我明天回公司验证一下，如果正确，马上结贴给分

[dawugui]

太多内容了,看了五分钟,不知道所云,如果能:

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

那就好办了.

[zhang_bamboo]

[Quote=引用 9 楼 dawugui 的回复:]
太多内容了,看了五分钟,不知道所云,如果能:


SQL code
最好给出完整的表结构,测试数据,计算方法和正确结果.


发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

那就好办了.
[/Quote]

没注意发帖之后可以编辑，下次会注意

[zhang_bamboo]

好像也不可以编辑嘛

[zhang_bamboo]

OK，运行正确，结贴给分