一个SQL语句的问题。高手帮帮忙

金拓 上海米修米修网络有限公司 CTO/CIO/技术副总裁/总工程师  2009-11-10 04:50:23
问题是这样的。
有3个表。
表1有字段id typeid cityid content
表2有字段typeid name
表3有字段cityid name
现在表1的typeid没有值,cityid有值,对应着表3的cityid
我现在想要更新表1中的typeid的值,要求是,表3里的name和表2里的name相等的typeid更新给表1的typeid。
比如:
表1里有一行是
id typeid cityid content
1 0 1 内容

表3中cityid=1的name是法国
表2中typeid=3的name是法国。
更新后,表1变成
id typeid cityid content
1 3 1 内容
...全文
53 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
金拓 2009-11-10
[Quote=引用 9 楼 fredrickhu 的回复:]
MYSQL  有请P梁
[/Quote]
P梁是谁啊?怎么找他

不管怎么说,先谢谢大家
回复
金拓 2009-11-10
[Quote=引用 4 楼 sql77 的回复:]
SQL codeUPDATE TB1SET TYPEID=T2.TYPEIDFROM TB2 T2,TB3 T3WHERE T2.NAME=T3.NAMEAND T2.CITYID=TB1.CITYID
[/Quote]

UPDATE cdb_threads SET TYPEID = T2.TYPEID FROM cdb_threadtypes T2,
cdb_threadcates T3 WHERE T2.NAME = T3.NAME AND T2.CATEID = cdb_threads.CATEID

MySQL 返回:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cdb_threadtypes T2,cdb_threadcates T3 WHERE

T2.NAME=T3.NAME AND T2.CATE' at line 1
回复
--小F-- 2009-11-10
[Quote=引用 7 楼 jintuo 的回复:]
引用 6 楼 fredrickhu 的回复:
SQL codeupdate
  表1 aset
  typeid=c.typeid,cityid=c.cityidfrom
  表1 a,表2 b,表3 cwhere
  c.typeidin (select typeidfrom 表2 )


UPDATE cdb_threads a SET typeid = c.typeid,
cateid = c.cateid FROM cdb_threads a,
cdb_threadtypes b,
cdb_threadcates c WHERE c.typeid IN (

SELECT typeid
FROM cdb_threadtypes
)

MySQL 返回:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from
  cdb_threads a,cdb_threadtypes b,cdb_threadcates c
where
  c.typeid in' at line 5
[/Quote]

MYSQL 有请P梁
回复
金拓 2009-11-10
[Quote=引用 2 楼 bancxc 的回复:]
SQL code 错了update tb1set tb1.typeid= tb2,typeidfrom tb1,tb2,tb3where tb1.cityid=tb3.cityidand tb2.name=tb3.name
[/Quote]

UPDATE cdb_threads SET cdb_threads.typeid = cdb_threadtypes.typeid FROM cdb_threads,
cdb_threadtypes,
cdb_threadcates WHERE cdb_threads.cateid = cdb_threadcates.cateid AND cdb_threadtypes.name = cdb_threadcates.name

MySQL 返回:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from cdb_threads,cdb_threadtypes,cdb_threadcates
where cdb_threads.cateid=cdb_' at line 2
回复
金拓 2009-11-10
[Quote=引用 6 楼 fredrickhu 的回复:]
SQL codeupdate
表1 aset
typeid=c.typeid,cityid=c.cityidfrom
表1 a,表2 b,表3 cwhere
c.typeidin (select typeidfrom 表2 )
[/Quote]

UPDATE cdb_threads a SET typeid = c.typeid,
cateid = c.cateid FROM cdb_threads a,
cdb_threadtypes b,
cdb_threadcates c WHERE c.typeid IN (

SELECT typeid
FROM cdb_threadtypes
)

MySQL 返回:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from
cdb_threads a,cdb_threadtypes b,cdb_threadcates c
where
c.typeid in' at line 5
回复
--小F-- 2009-11-10
update
表1 a
set
typeid=c.typeid,cityid=c.cityid
from
表1 a,表2 b,表3 c
where
c.typeid in (select typeid from 表2 )
回复
laker_914 2009-11-10
最近好多贴子都是关联更新的问题哟
回复
SQL77 2009-11-10
UPDATE TB1 SET TYPEID=T2.TYPEID FROM TB2 T2,TB3 T3 WHERE T2.NAME=T3.NAME AND T2.CITYID=TB1.CITYID
回复
SQL77 2009-11-10
UPDATE TB1 SET TYPEID=T2.TYPEID FROM TB2 T2,TB3 T3 WHERE T2.NAME=T3.NAME
?
回复
bancxc 2009-11-10
 错了
update tb1 set tb1.typeid = tb2,typeid
from tb1,tb2,tb3
where tb1.cityid=tb3.cityid and tb2.name=tb3.name
回复
bancxc 2009-11-10

update tb1 set tb1.typeid = tb2,typeid
from tb1,tb2,tb3
on tb1.cityid=tb3.cityid and tb2.name=tb3.name
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2009-11-10 04:50
社区公告
暂无公告