一个SQL语句的简单问题(50分)

I

iranjn

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #1

有两个表table1和table2 我想设table1的state字段（已有字段）为 2（字符型）
条件是table1的id字段等于table2的id字段，table1的state字段不等于table2的state字段
table2的state字段等于2（字符型），SQL语句怎么写？

 

A

Awen.ZW.Yang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #2

UPDATE Table1 set State = '2'
FROM Table1 JOIN Table2 ON Table1.id = Table2.id --table1的id字段等于table2的id字段
AND Table1.State <> Table2.State --table1的state字段不等于table2的state字段
AND Table2.State = '2' --table2的state字段等于2（字符型）
=>--简化
UPDATE Table1 set State = '2'
FROM Table1 JOIN Table2 ON Table1.id = Table2.id
AND Table1.State <> '2'

 

G

gxcooo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #3

update table1 set state='2' where state<>'2' and id in(select id from table2 where state='2')
不敢确定

 

L

lanbing1400

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #4

update table1 set state='2' where table1.id=table2.id and table1.state<>table2.state and table2.state='2'

 

W

windbell

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #5

UPDATE Table1 set table1.State = '2'
FROM Table1
where table1.id=table2.id
and table1.state<>table2.state
and table2.state='2'

 

H

hbwyq

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #6

update table1 set table1.state='2'
from table2 where table1.id=table2.id and table2.state='2'
--table1.state<>table2.state 这句多余

 

沙

沙_儿

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #7

UPDATE Table1 set table1.State = '2'
FROM Table1,Table2
where table1.id=table2.id
and table1.state<>table2.state
and table2.state='2'

 

I

iranjn

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-27

• #8

多人接受答案了。