tbl_aid name sex
1 kate 2
2 jim 1
3 julit 2
tbl_bid code name
1 1 男
2 2 女我想将a表sex列 换成对应的男或女
update tbl_a set tbl_a.sex=(select b.name from tbl_b b where b.code=tbl_a.sex)这么写不对,请问该怎么改?
不用case ....when...的写法
1 kate 2
2 jim 1
3 julit 2
tbl_bid code name
1 1 男
2 2 女我想将a表sex列 换成对应的男或女
update tbl_a set tbl_a.sex=(select b.name from tbl_b b where b.code=tbl_a.sex)这么写不对,请问该怎么改?
不用case ....when...的写法
set sex = (select name from tbl_b b where b.code = a.sex)当然sex的数据类型和name的数据类型必须一样
再执行
update tbl_a set tbl_a.sex=(select b.name from tbl_b b where b.code=tbl_a.sex)
/
create table tbl_b (id integer,code varchar2(10),name varchar2(10));
/
insert into tbl_a values(1 , 'kate' , '2');
insert into tbl_a values(2 , 'jim' , '1');
insert into tbl_a values(3 , 'julit' , '2');insert into tbl_b values(1 , '1' , '男');
insert into tbl_b values(2 , '2' , '女');
/
SQL> select * from tbl_a;
ID NAME SEX
--------------------------------------- ---------- ----------
1 kate 2
2 jim 1
3 julit 2
SQL> select * from tbl_b;
ID CODE NAME
--------------------------------------- ---------- ----------
1 1 男
2 2 女
SQL> update tbl_a set tbl_a.sex = (select b.name from tbl_b b where b.id = tbl_a.sex);
3 rows updated
SQL> select * from tbl_a;
ID NAME SEX
--------------------------------------- ---------- ----------
1 kate 女
2 jim 男
3 julit 女
SQL>
commit;
insert into tal_a values(2,'jim','1');
insert into tal_a values(3,'julit','2');insert into tal_b values(1,'1','M');
insert into tal_b values(2,'2','F');update tal_a set tal_a.sex=(select b.name from tal_b b where b.code=tal_a.sex)
正确执行