一道面试题

今天在javaeye论坛上看大一道数据库面试题
总结一下：
两张表：
A:id,age,column1,column2
B:id,age,column1,column2
A.id 与 B.id关联。
将B中age>40的column1,column2更新到A的相应记录中，只能写一个语句。
写出mysql和oracle两个版本。

update A   
set A.column1 = (   
    select b.column1   
    from B where B.id = A.id   
    and B.age > 40   
    ),   
    A.column2 = (   
    select b.column2   
    from B where B.id = A.id   
    and B.age > 40   
    )  

oracle && mysql

： 
update A set (c1,c2)=(select c1,c2 from B where id=a.id and age>40) 
where id in (select id from b where age>40) 

SQL2000:

update a set column1=b.column1 ,column2=b.column2 from a ,b where a.id = b.id and b.age>40 

armorking 写道

aimer311 写道

armorking 写道

oracle

update A
set A.column1 = (
    select b.column1
    from B where B.id = A.id
    and B.age > 40
    ),
    A.column2 = (
    select b.column2
    from B where B.id = A.id
    and B.age > 40
    )

我基本上思路跟这个一样，想不出mysql跟oracle有什么不一样

update A
set A.column1 = (
    select b.column1
    from B where B.id = A.id
    and B.age > 40
    ),
    A.column2 = (
    select b.column2
    from B where B.id = A.id
    and B.age > 40
    ) where A.id=B.id and B.age>40

因为是A,B相应记录,所以我加上了where条件。
感觉好别扭。

where条件是必须的
不加条件的话，A表中的所有记录都会被更新
对于A中不满足A.id=B.id and B.age>40条件的记录而言，
column1和column2会被更新为null

不过where条件可以用IN或者EXISTS的形式
如
方式一
where A.id in (select B.id from B where B.id = A.id and B.age > 40)
或者
方式二
where exists (select 1 from B where B.id = A.id and B.age > 40)

当然直接用如下形式的条件也是可以的
方式三
where A.id = (select 1 from B where B.id = A.id and B.age > 40)

不过，方式三的情况下，当B中存在重复id的时候，可能会报ora-01427错误，
因为这种方式会要求作为条件值的子查询只有一个返回值

所以，子查询中还应当加上distinct或者rownum=1这样的限制
再参照ls的合并更新项目的做法，sql文如下

update A
set (A.column1, A.column2) = (
    select B.column1, B.column2
    from B where B.id = A.id
    and B.age > 40
    and rownum = 1
    )
where exists (
    select B.id 
    from B 
    where B.id = A.id and B.age > 40
)

以下是测试用的建表语句

create table A (
    id      varchar2(1),
    column1 varchar2(1),
    column2 varchar2(1)
)
/
create table B (
    id      varchar2(1),
    column1 varchar2(1),
    column2 varchar2(1),
    age     number
)
/

delete from A;
insert into A(id, column1, column2) values ('1', '1', '1');
insert into A(id, column1, column2) values ('1', '1', '1');
insert into A(id, column1, column2) values ('2', '2', '2');
insert into A(id, column1, column2) values ('3', '3', '3');

delete from B;
insert into B(id, column1, column2, age) values ('1', 'X', 'X', 41);
insert into B(id, column1, column2, age) values ('2', 'X', 'X', 21);
insert into B(id, column1, column2, age) values ('3', 'X', 'X', 51);
insert into B(id, column1, column2, age) values ('3', 'Y', 'Y', 51);
commit;

ps:我个人认为，把这个问题作为面试题，并不见得合适