求助这样的SQL怎么写？(100分)

Question

已知A：id name age address postcode
B:id name age address postcode
要求  把B的age大于30的address, postcode更新到A的address,postcode中，id关联如果用oracle怎么写？用MYsql怎么写？谢谢！！！

Answer 1

update a set (address,postcode) = (select address,postcode from b where a.id = b.id and b.age > 30) 
where exists (select 1 from b where a.id = b.id and b.age > 30);这是oracle的

Answer 2

update a, b set a.address = b.address, a.postcode = b.postcode where a.id = b.id and b.age > 30;这是mysql的

Answer 3

其中oracle语句未测试，mysql语句在mysql 5.051b community版中测试通过

Answer 4

呵呵 我帮你测一下 是可以的
SQL> select * from a;        ID NAME                        AGE ADDRESS                POSTCODE
---------- -------------------- ---------- -------------------- ----------
         1 wh                           10 sh                       200000
         2 wp                           12 sh                       200000
         3 wr                           17 sh                       200000SQL> select * from b;        ID NAME                        AGE ADDRESS                POSTCODE
---------- -------------------- ---------- -------------------- ----------
         2 wp                           35 bj                       100000
         1 wh                           45 bj                       100000SQL> update a set (address,postcode) = (select address,postcode from b where a.i
d=b.id and b.age>30) where exists (select 1 from b where a.id=b.id and b.age>30)
;2 rows updated.SQL> select * from a;        ID NAME                        AGE ADDRESS                POSTCODE
---------- -------------------- ---------- -------------------- ----------
         1 wh                           10 bj                       100000
         2 wp                           12 bj                       100000
         3 wr                           17 sh                       200000SQL>

Answer 5

update a
   set (a.address, a.postcode) = (select b.address, b.postcode
                                    from b
                                   where a.id = b.id
                                     and b.age > 30)
 where exists (
              select 1
              from b
               where a.id = b.id
               and b.age > 30)

Answer 6

update a set (address,postcode) = (select address,postcode from b where a.id = b.id and b.age > 30) 
where exists (select 1 from b where a.id = b.id and b.age > 30);