ORACLE DELETE with converted columns-CodePudding

I'm transferring data from Oracle. There is table named A and when there are overlapping columns in the process of moving them to table C, I am trying to delete them and put them in. However, if A and C have the same column configuration, it works smoothly, but the column configuration is different, so I don't know what to do if I convert it.

What I've tried so far is as follows.

CREATE TABLE test.test_pk_a
(
  col_one   VARCHAR2(4) NOT NULL,
  col_two   VARCHAR2(5) NOT NULL,
  col_three VARCHAR2(8) NOT NULL,
  CONSTRAINT test_pk_a_pk PRIMARY KEY(col_one,col_two)
);

INSERT INTO test_pk_a VALUES('A',1,1);
INSERT INTO test_pk_a VALUES('A',2,1);
INSERT INTO test_pk_a VALUES('A',3,1);


CREATE TABLE test.test_pk_c
(
  col_one_v   VARCHAR2(4) NOT NULL,
  col_two_v   VARCHAR2(5) NOT NULL,
  col_three_v VARCHAR2(8) NOT NULL,
  CONSTRAINT test_pk_c_pk PRIMARY KEY(col_one_v,col_two_v)
);

INSERT INTO test_pk_c VALUES(10,'c',1);
INSERT INTO test_pk_c VALUES(20,'a',1);


DELETE 
  FROM (SELECT * 
          FROM test.test_pk_a A, test.test_pk_c C
         WHERE A.col_two*10 = C.col_one_v 
           AND LOWER(A.col_one)= C.col_two_v);

How should I modify this Query in order to make it work?

CodePudding user response：

If you've meant to delete from the table A, then you can precede the subquery with EXISTS such as

DELETE test_pk_a
 WHERE EXISTS (SELECT 0
                 FROM test_pk_a A
                 JOIN test_pk_c C
                   ON A.col_two * 10 = C.col_one_v
                  AND LOWER(A.col_one) = C.col_two_v);

if you meant the common column names by configuration wouldn't be the case(no problem whether to have the same column names or not)