INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Nick
    #1

    INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

    Im trying to insert a bunch of rows into a table. If the row already
    exists id like to update the row 'counter'. For example...

    INSERT INTO table1
    SELECT field1, field2
    FROM table2
    ON DUPLICATE KEY UPDATE field2 = 1

    Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY?
    I cant get it to work.

    -Nick
    Tags: None
    • Bill Karwin
      #2
      Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

      Nick wrote:[color=blue]
      > Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick[/color]

      Okay, then can you be more specific regarding not being able to get the
      feature to work. It looks like you are trying to set field2 to the
      value 1 on a failed insert. I'll make a guess you meant to increment
      the value of field2, in which case you should do this:

      INSERT INTO table1
      SELECT field1, field2
      FROM table2
      ON DUPLICATE KEY UPDATE field2 = field2 + 1

      Refer to http://dev.mysql.com/doc/mysql/en/INSERT.html for more information.

      Regards,
      Bill K.

        Comment

        • Bill Karwin
          #3
          Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

          Nick wrote:[color=blue]
          > Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY?
          > I cant get it to work.[/color]

          I haven't used it, but I noticed the docs say that this feature is new
          as of MySQL 4.1.0. Are you using that version of MySQL or later?

          Regards,
          Bill K.

            Comment

            • Nick
              #4
              Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

              Sorry for the typo Bill. I tried using the above example you gave me
              which returns the error message:

              Syntax error or access violation, message from server:
              You have an error in your SQL syntax. Check the manual that
              corresponds to your MySQL server version for the right syntax to use.

              The MySQL documentation link doesnt show the option for ON DUPLICATE
              KEY for INSERT... SELECT...

              Were you able to get it to work? -Nick

                Comment

                • Nick
                  #5
                  Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

                  Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick

                    Comment

                    • Bill Karwin
                      #6
                      Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

                      Nick wrote:
                      [color=blue]
                      > The MySQL documentation link doesnt show the option for ON DUPLICATE
                      > KEY for INSERT... SELECT...[/color]

                      On page http://dev.mysql.com/doc/mysql/en/INSERT.html:

                      "If you use an INSERT ... VALUES statement with multiple value lists or
                      INSERT ... SELECT, the statement returns an information string in this
                      format:
                      ...."
                      [color=blue]
                      > Were you able to get it to work? -Nick[/color]

                      No, I'm using MySQL 4.0.20. So I can't offer any advice from my own
                      experience. I agree that it's a useful feature, but it's not standard
                      SQL. Since it's not working, it might be easier to code around it the
                      old-fashioned way. That is, fetch the dataset you want to insert, and
                      filter out entries that already exist in the destination table.

                      Regards,
                      Bill K.

                        Comment

                        • Bill Karwin
                          #7
                          Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

                          Nick wrote:[color=blue]
                          > Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick[/color]

                          Okay, then can you be more specific regarding not being able to get the
                          feature to work. It looks like you are trying to set field2 to the
                          value 1 on a failed insert. I'll make a guess you meant to increment
                          the value of field2, in which case you should do this:

                          INSERT INTO table1
                          SELECT field1, field2
                          FROM table2
                          ON DUPLICATE KEY UPDATE field2 = field2 + 1

                          Refer to http://dev.mysql.com/doc/mysql/en/INSERT.html for more information.

                          Regards,
                          Bill K.

                            Comment

                            • Nick
                              #8
                              Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

                              Sorry for the typo Bill. I tried using the above example you gave me
                              which returns the error message:

                              Syntax error or access violation, message from server:
                              You have an error in your SQL syntax. Check the manual that
                              corresponds to your MySQL server version for the right syntax to use.

                              The MySQL documentation link doesnt show the option for ON DUPLICATE
                              KEY for INSERT... SELECT...

                              Were you able to get it to work? -Nick

                                Comment

                                • Bill Karwin
                                  #9
                                  Re: INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

                                  Nick wrote:
                                  [color=blue]
                                  > The MySQL documentation link doesnt show the option for ON DUPLICATE
                                  > KEY for INSERT... SELECT...[/color]

                                  On page http://dev.mysql.com/doc/mysql/en/INSERT.html:

                                  "If you use an INSERT ... VALUES statement with multiple value lists or
                                  INSERT ... SELECT, the statement returns an information string in this
                                  format:
                                  ...."
                                  [color=blue]
                                  > Were you able to get it to work? -Nick[/color]

                                  No, I'm using MySQL 4.0.20. So I can't offer any advice from my own
                                  experience. I agree that it's a useful feature, but it's not standard
                                  SQL. Since it's not working, it might be easier to code around it the
                                  old-fashioned way. That is, fetch the dataset you want to insert, and
                                  filter out entries that already exist in the destination table.

                                  Regards,
                                  Bill K.

                                    Comment

                                    Previous template Next
                                    Working...