Hi everyone,
I have an insert statement in sql query that need to be in plsql I believe which I'm looking to according to what I'm going to explain below.
Here are my tables
CREATE TABLE "MY_EMAILS"
( "EMAIL_ID" NUMBER,
"EMAILS_LIST" VARCHAR2(4000 BYTE));
CREATE TABLE "MAILCTL"
( "EMAIL_ID" NUMBER,
"FROM_EMAIL" VARCHAR2(4000 BYTE),
"TO_EMAIL" VARCHAR2(4000 BYTE));
The below insert has no problem, but for showing the values
INSERT INTO my_emails VALUES(1, 'george@mail.com');
INSERT INTO my_emails VALUES(2, 'fatma@mail.com');
INSERT INTO my_emails VALUES(3, 'meriam@mail.com');
INSERT INTO my_emails VALUES(4, 'william@mail.com');
INSERT INTO my_emails VALUES(5, 'robert@mail.com');
INSERT INTO my_emails VALUES(6, 'emily@mail.com');
INSERT INTO mailctl VALUES(1, 'x@mail.com', 'y@mail.com');
Here, the below insert statement is good and it has defect for my requirement, which is when I start inserting from one table to another table, it assigns the same email_id value number for each row in mailctl table. Where it has to check the maximum value in the mailctl table and increase by 1 based on number exist or of each new rows added in one shot. second thing, I want the below statement to add all the rows from one table to another table in one column to append and add comma for each in one record. So I hope someone can help me in in putting the below Insert statement with plsql to add correct number and insert values into one column in one shot.
INSERT INTO mailctl(email_id, from_email, to_email)
SELECT
(SELECT max(email_id) FROM mailctl)+1 as mailid,
'abcd@mail.com',
emails_list
FROM
my_emails;
In the below select statement, it repeated the same number 2 for rows which is wrong and it has to give the third row number 3 and fourth row number 4 and so on.
SELECT * FROM mailctl;
EMAIL_ID FROM_EMAIL TO_EMAIL
1 x@mail.com y@mail.com
2 abcd@mail.com george@mail.com
2 abcd@mail.com fatma@mail.com
2 abcd@mail.com meriam@mail.com
2 abcd@mail.com william@mail.com
2 abcd@mail.com robert@mail.com
2 abcd@mail.com emily@mail.com
However, what I’m looking for is the output result of mailctl, which I hope to have this result as below, and here I’m imagining this output when I do select statement. Therefore, the Insert statement that I want to function is to check the maximum value in mailctl table that has list of all emails to bring them into one record and adding comma between each email.
SELECT * FROM mailctl;
EMAIL_ID FROM_EMAIL TO_EMAIL
1 abcd@mail.com george@mail.com,fatma@mail.com,meriam@mail.com,william@mail.com,robert@mail.com,emily@mail.com
Thank you very much for your help and support.