这种SQL怎么写，多对多的关系，Update问题，求教

[LunTanZeng]

有两个表 \
table1:
ID Name
-------------------
1 aa
1 bb
2 cc
3 dd
4 ee
5 aa
...................

table2

sID sName
-------------------
aa
bb
cc
ee
aa
...................

现在要更新表table2
结果如下
sID sName
-------------------
1 aa
1 bb
2 cc
4 ee
1 aa
.............

[bzszp]

UPDATE TABLE2 SET SID=(SELECT MIN(ID) FROM TABLE1 WHERE TABLE1.NAME=TABLE2.NAME)
WHERE EXISTS(SELECT ID FROM TABLE1 WHERE TABLE1.NAME=TABLE2.NAME);

[ericxieforever]

更新时取table1表中name重复的纪录的id的规则是什么？比如aa的选择？是选ID小的吗？

[iamme81]

不必那么麻烦吧!

insert into table1 select 1,'aa'
union select 1,'bb'
union select 2,'cc'
union select 3,'dd'
union select 4,'ee'

insert into table2 select NULL,'aa'
union select NULL,'bb'
union select NULL,'cc'
union select NULL,'ee'
union select NULL,'aa'


update table2 set SID=ID
from table1,table2
where table1.Name=table2.SName

select * from table2

1 aa
1 bb
2 cc
1 aa
4 ee

[ericxieforever]

楼上你用SQL-server吧，语法和oracle不一样的

[tcmakebest]

看上去表2都没有主键的,如何更新呢?

[knowledge_Is_Life]

没遇到过这种情况.