SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL - Insert into a Table from another Table - with a condition

    Code:
    insert into tbl1 (value1,value2)
    select value3, value4 from tbl2;
    The above code inserts value3 and value4 from tbl2 INTO value1 & value2 of tbl1.

    What I would like to do is add a "WHEN" statement, that first compares the two tables to make sure the combination of those two values do not exist before adding them.
    EG: when tbl1.value1 & tbl1.value2 != tbl2.value3 & tbl2.value4

    How would I properly write the "when" statement and incorporate it into my sample above?

    (NOTE: I have added a UNIQUE property to the database so both value1 and value2 of tbl1 must be unique, but that will throw a duplicate error if the SQL is run, and nothing new gets entered if it appears later than the error)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by codamedia View Post
    (NOTE: I have added a UNIQUE property to the database so both value1 and value2 of tbl1 must be unique, but that will throw a duplicate error if the SQL is run, and nothing new gets entered if it appears later than the error)
    the UNIQUE index was the correct strategy

    now, change your query to this --
    Code:
    INSERT IGNORE
      INTO tbl1 
         ( value1, value2 )
    SELECT value3, value4 
      FROM tbl2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the difference between 1st one and second one. I think second one will be more useful as compared to 1st. Because you need not have to manually remove duplicate from table and time also will be reduced.

  4. #4
    Non-Member spaculus's Avatar
    Join Date
    Mar 2012
    Location
    Vadodara
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use the second query after condition means after where keywords.


Bookmarks

Posting Permissions

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