1.有表t1(prod_id, prod_type)和t2(pord_id, chn_id)
使用一条语句实现,如果t1表中的prod_id对应t2表中的一条记录则prod_type置为s,若对应多条记录则置为m.2.有表t1(id_1, field_1, field_2)和t2(id_2, field_1, field_2, field_3),
t1为空表,id_1为主键,使用sequence(seq_1)实现,t2有数据,id_2为主键,使用seq_2填写,
现需要将t_2中的数据插入t_1中,如何实现???
使用一条语句实现,如果t1表中的prod_id对应t2表中的一条记录则prod_type置为s,若对应多条记录则置为m.2.有表t1(id_1, field_1, field_2)和t2(id_2, field_1, field_2, field_3),
t1为空表,id_1为主键,使用sequence(seq_1)实现,t2有数据,id_2为主键,使用seq_2填写,
现需要将t_2中的数据插入t_1中,如何实现???
update
t1
set prod_type=
(
select decode(c.num,1,'S','M')
from(
select a.prod_id,count(*) num
from t1 a,t2 b
where a.prod_id=b.prod_id
group a.prod_id
) c
where t1.prod_id=c.prod_id
)
insert into t1
select seq_1.nextval,field_1, field_2
from t2
CREATE TABLE t1(prod_id NUMBER, prod_type CHAR(1));
CREATE TABLE t2(pord_id NUMBER, chn_id NUMBER) ;
INSERT INTO t1 VALUES(1,NULL);
INSERT INTO t1 VALUES(2,NULL);
INSERT INTO t1 VALUES(3,NULL);
INSERT INTO t1 VALUES(4,NULL);INSERT INTO t2 VALUES(1,1);
INSERT INTO t2 VALUES(1,2);
INSERT INTO t2 VALUES(2,3);
INSERT INTO t2 VALUES(3,4);
INSERT INTO t2 VALUES(3,5);
COMMIT;
SELECT * FROM t2;
SELECT * FROM t1;
问题1代码:
UPDATE T1
SET PROD_TYPE = (SELECT CASE
WHEN COUNT(1) = 1 THEN
's'
WHEN COUNT(1) > 1 THEN
'm'
ELSE
' '
END CASE
FROM T2
WHERE T1.PROD_ID = T2.PORD_ID);问题2代码:
INSERT INTO t1 SELECT seq_1.nextval,field_1,field_2 FROM t2;