Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Arrow Unanswered: Need a single query to perform this...

    Hi,
    I have two table of same structure.
    Table1;TABLE2
    consisting of 45 columns.
    I want to perform following operations:

    1.INSERT INTO TABLE2
    SELECT * FROM TABLE1 A
    WHERE
    A.COL1 = 'RL' AND
    A.COL2 IN ('CT','3W','ND') AND
    A.COL3 = 'CO' AND
    A.COL4 = '000' AND
    A.COL5 IS NULL AND
    A.COL6 = 0

    Update entries in TABLE2 with COL10='543210',COL11=SYSDATE and
    replacing col4 with ('111','660','668','980','730')
    (so here seperate entries for each of the above listed value should be created)

    insert Table2 entries into table1

    2.INSERT INTO TABLE2
    SELECT * FROM TABLE1 A
    WHERE
    A.COL1 = 'RL' AND
    A.COL2 = 'TP' AND
    A.COL3 = 'CO' AND
    A.COL4 = '000' AND
    A.COL5 IN ('CO','D','OO') AND
    A.COL6 = 0

    Update entries in TABLE2 with COL10='543210',COL11=SYSDATE and
    replacing col4 with ('111','660','668','980','730')
    (so here seperate entries for each of the above listed value should be created)

    insert Table2 entries into table1

    Now the problem is i need to write a query to accomplish this..I need to perform this with a sinqle query
    rather writing a proc with seperate query statements for each operation.
    I know it will be simple if we write proc...but i have been informed specifically to use a single
    query statement..
    ANy idea????
    Thanks & Regards
    Manikandan

  2. #2
    Join Date
    Mar 2003
    Posts
    12
    Hi Manikandan,

    If my understanding is right, you are basically trying to replace some of your table1 data but put it in a new set of rows without disturbing the existing data??

    Could you please provide the conditions for replacing COL4 values? like replacing '111' under what circumstances etc...

    Rgds.

  3. #3
    Join Date
    Aug 2002
    Location
    India
    Posts
    60
    Hi,
    Thanks a lot..You are very correct.
    No conditions for updating the rows

    Suppose i have 50 rows in Table2.
    all the rows should be updated with col4='111'
    all the rows should be updated with col4='660'
    all the rows should be updated with col4='668'
    all the rows should be updated with col4='980'
    all the rows should be updated with col4='730'

    so finally i`ll have 250 rows to be inserted into table1.
    Thanks & Regards
    Manikandan

  4. #4
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Need a single query to perform this...

    Originally posted by Manikandan
    Hi,
    I have two table of same structure.
    Table1;TABLE2
    consisting of 45 columns.
    I want to perform following operations:

    1.INSERT INTO TABLE2
    SELECT * FROM TABLE1 A
    WHERE
    A.COL1 = 'RL' AND
    A.COL2 IN ('CT','3W','ND') AND
    A.COL3 = 'CO' AND
    A.COL4 = '000' AND
    A.COL5 IS NULL AND
    A.COL6 = 0

    Update entries in TABLE2 with COL10='543210',COL11=SYSDATE and
    replacing col4 with ('111','660','668','980','730')
    (so here seperate entries for each of the above listed value should be created)

    insert Table2 entries into table1

    2.INSERT INTO TABLE2
    SELECT * FROM TABLE1 A
    WHERE
    A.COL1 = 'RL' AND
    A.COL2 = 'TP' AND
    A.COL3 = 'CO' AND
    A.COL4 = '000' AND
    A.COL5 IN ('CO','D','OO') AND
    A.COL6 = 0

    Update entries in TABLE2 with COL10='543210',COL11=SYSDATE and
    replacing col4 with ('111','660','668','980','730')
    (so here seperate entries for each of the above listed value should be created)

    insert Table2 entries into table1

    Now the problem is i need to write a query to accomplish this..I need to perform this with a sinqle query
    rather writing a proc with seperate query statements for each operation.
    I know it will be simple if we write proc...but i have been informed specifically to use a single
    query statement..
    ANy idea????

    Hi,
    Why dont you write a database trigger for the same ?
    i dont understand this concept of a single statement.
    nn

  5. #5
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Arrow

    Hi..
    Thanks alot...I have completed by writing a proc.....but neway is there any possibility of using CASE statement???
    Thanks & Regards
    Manikandan

  6. #6
    Join Date
    Mar 2003
    Posts
    12
    Please try this ... hope it works.

    Cheers!
    Srini.

    insert into table1
    select col1,col2,col3,replace(col4,'000','111'), ... ,'543210'(a.col10),sysdate(a.col11) from table1 where (COL1 = 'RL' AND COL2 IN ('CT','3W','ND') AND COL3 = 'CO' AND COL4 = '000' AND COL5 IS NULL AND COL6 = 0) OR (COL1 = 'RL' AND COL2 = 'TP' AND COL3 = 'CO' AND COL4 = '000' AND COL5 IN ('CO','D','OO') AND COL6 = 0)
    union
    select col1,col2,col3,replace(col4,'000','660'), ... ,'543210'(a.col10),sysdate(a.col11) from table1 where (COL1 = 'RL' AND COL2 IN ('CT','3W','ND') AND COL3 = 'CO' AND COL4 = '000' AND COL5 IS NULL AND COL6 = 0) OR (COL1 = 'RL' AND COL2 = 'TP' AND COL3 = 'CO' AND COL4 = '000' AND COL5 IN ('CO','D','OO') AND COL6 = 0)
    union
    select col1,col2,col3,replace(col4,'000','668'), ... ,'543210'(a.col10),sysdate(a.col11) from table1 where (COL1 = 'RL' AND COL2 IN ('CT','3W','ND') AND COL3 = 'CO' AND COL4 = '000' AND COL5 IS NULL AND COL6 = 0) OR (COL1 = 'RL' AND COL2 = 'TP' AND COL3 = 'CO' AND COL4 = '000' AND COL5 IN ('CO','D','OO') AND COL6 = 0)
    union
    select col1,col2,col3,replace(col4,'000','980'), ... ,'543210'(a.col10),sysdate(a.col11) from table1 where (COL1 = 'RL' AND COL2 IN ('CT','3W','ND') AND COL3 = 'CO' AND COL4 = '000' AND COL5 IS NULL AND COL6 = 0) OR (COL1 = 'RL' AND COL2 = 'TP' AND COL3 = 'CO' AND COL4 = '000' AND COL5 IN ('CO','D','OO') AND COL6 = 0)
    union
    select col1,col2,col3,replace(col4,'000','730'), ... ,'543210'(a.col10),sysdate(a.col11) from table1 where (COL1 = 'RL' AND COL2 IN ('CT','3W','ND') AND COL3 = 'CO' AND COL4 = '000' AND COL5 IS NULL AND COL6 = 0) OR (COL1 = 'RL' AND COL2 = 'TP' AND COL3 = 'CO' AND COL4 = '000' AND COL5 IN ('CO','D','OO') AND COL6 = 0)

  7. #7
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Arrow

    Hi,
    Thanks a lot...
    Thanks & Regards
    Manikandan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •