Link to home
Create AccountLog in
Avatar of new_perl_user
new_perl_user

asked on

SQL QUERY HELP

Hi,
 I need to insert data into table 1 by getting info from table table 2 based on conditions from table2 and table3.

table1:
sequence
date

table2:
sequence,
id

table3:
id,
date

so I have to insert data into table1(sequence column)  from table2(sequence column) by comparing table 3  id and table2 id.

I tried something like this :

INSERT INTO table1 (SEQUENCE_NUMBER, ERROR_RECEIVED_DATE)
   (SELECT table2.SEQUENCE_NUMBER,
           SYSDATE
           FROM table2,
          table3
           WHERE table2.ID =
      table3.ID)

error:  ORA-00001: unique constraint (table_PK) violated.

I think this is happening because table3 has duplicate data for ID. but i can do anything for this data to remove duplicates. it should some how select the unique ID from table 3
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what about this:
INSERT INTO table1 (SEQUENCE_NUMBER, ERROR_RECEIVED_DATE)
   (SELECT table2.SEQUENCE_NUMBER,
           SYSDATE
           FROM table2
         WHERE EXISTS( SELECT NULL FROM table3 WHERE table2.ID = table3.ID)
  )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
delete from table 3 where rowid  in (
select max(rowid) from table 3
GROUP BY id
HAVING COUNT(*) > 1);
Insert InTo
      Table1 T1
(
      SEQUENCE_NUMBER,
      ERROR_RECEIVED_DATE
)
(
Select
      T2.SEQUENCE_NUMBER,
      T3.SYSDATE
From
      Table2 T2
Inner Join Table3 T3 On T3.ID = T2.ID
Where
      Not Exists(Select T1.SEQUENCE_NUMBER From Table1 T1 Where T1.SEQUENCE_NUMBER = T2.SEQUENCE_NUMBER)
)
The error clearly shows there is a constraint in table1, and you are trying to insert duplicate values. This is the reason. Now you should be clear on what data you have to store in table1. Hope this comment is useful.
INSERT INTO table1 (SEQUENCE_NUMBER, SYSDATE)
   (
        SELECT DISTINCT table2.SEQUENCE_NUMBER FROM table2, table3
        WHERE table2.ID = table3.ID
   )

If we are inserting sysdate anyway, why do we select it, we can always insert that directly.

Also,
You may get this error if the table1 already has the data with the same id.

So, make sure that you clear the content of table1 if you are rerunning the query.