Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Performing an Insert using NOT EXIST

    I have two tables that I have to compare:

    Code:
    Table:PR
    WBS1            WBS2              WBS3
    123-456         1000                  01
    123-456         1000                  02
    123-456         2000                  02
    567-890         2000                  01
    567-890         2000                  02
    
    Table:PR_Template
    WBS2      WBS3
    1000        00
    1000        01
    1000        02
    2000        00
    2000        01
    2000        02
    
    After Insert I should have:
    
    wbs1            wbs2              wbs3
    123-456        1000                 00
    123-456        1000                 01
    123-456        1000                 02
    123-456        2000                 00
    123-456        2000                 01
    123-456        2000                 02
    567-890        1000                 00
    567-890        1000                 01
    567-890        1000                 02
    567-890        2000                 00
    567-890        2000                 01
    567-890        2000                 02

    Basically, I need to insert the wbs2 and wbs3 where it does not exist in each wbs1.

    What I have now will find the values that need to be inserted for a particular project but I don't know how to go through each project and perform the insert:

    Select * from PR_template Where Not Exists
    (Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
    And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')
    Order by wbs2, wbs3 asc

    Thank You

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    INSERT INTO PR(WBS1, WBS2, WBS3)
    Select WBS1, WBS2, WBS3
    from PR_template Where Not Exists
    (Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
    And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')


    And lose the order by
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Posts
    105
    Minus the 'Insert' that is kindof what I already had ( I used 'Select' just for testing purposes to see if I could find the instances of where there was no existence). The problem is that I am trying to perform the insert on all WBS1's at once in the PR table. Notice however that there is no WBS1 in the PR_Template table.

    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    insert into PR
    select X.WBS1
         , PRT.WBS2
         , PRT.WBS3
      from ( select distinct WBS1
               from PR
           ) as X
    cross
      join PR_Template as PRT
     where not exists
           ( select *
               from PR
              where WBS1 = X.WBS1
                and WBS2 = PRT.WBS2
                and WBS3 = PRT.WBS3 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2004
    Posts
    105
    Perfect.

    Thanks so much

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, my bad...

    Aside from the cartesian product mentioned by Rudy, how would you define existance for data that doesn't exists?

    How do you know what data to look for for w1 and w2 that match up with a w3?

    Unless you have some rules, or unless you want to match it up to everything (like Rudy's example), you can't do this.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by lauramccord
    Perfect.

    Thanks so much

    Really?

    Better set up a good test bed
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sour grapes, old man?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    sour grapes, old man?
    Not really.

    Do you really think that's the solution?

    If you they just want to make up data for the sake of having data, then ok.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Do you really think that's the solution?
    well, i know SQL pretty well, and perhaps i'm unrealistically overconfident, but i have to admit that i did not set up a test bed for this particular problem, i just wrote the SQL based on my understanding of the problem

    so yeah, i really think that's the solution, and i think laura is happy with it too

    If you they just want to make up data for the sake of having data, then ok.
    if you they do, then you they will be happy too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does Laura understand that for Every unique value of WBS1 it will be paired with every unique WBS2 And WBS3, that doesn't already exist.

    Now if the code is not doing that, then I missed the boat.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Does Laura understand that for Every unique value of WBS1 it will be paired with every unique WBS2 And WBS3, that doesn't already exist.
    i'm confident that she understands this, because that is, in fact, what she asked for!!

    you could, as they say, look it up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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