2 Replies Latest reply on Feb 3, 2009 11:44 AM by fober1

    new update query?

      I am trying to update a record but i have one unique things in my update statement.

      I have the link name which is unique. if i disable the link name to not to be updated then my update works fine.

      What if i want to also change the link.

      if i enter new link and that new link also exists in other record, then it should throw an error. if i keep the same link and update otehr values, then it should update the record.

      Hope u guys under stand what i am trying to do..

      it is something usage of If Exists but i don't know the exact way to do it.

      Please guide me


        • 1. Re: new update query?
          Level 7
          assuming col2 is set up with UNIQUE index option, and 'newvalue' is not
          same as in some other row in the table, this should work just fine:

          UPDATE mytable
          col1 = 'someting',
          col2 = 'newvalue'
          WHERE col2 = 'oldvalue'

          this should also work fine, if you need to update other columns, but not

          UPDATE mytable
          SET col1 = 'someting'
          WHERE col2 = 'oldvalue'

          are you sure you don;t have some other constraints, maybe on some other
          columns, interfering with your update?

          posting your code may help...

          Azadi Saryev
          • 2. Re: new update query?

            There's no single SQL function that allows you to do that, you have to build the logic in ColdFusion.

            To make that work you need to first query if there is already a record with that link in the DB and a different key then your current record.
            If yes, then throw a CF error message, if not then do the db update.

            I don't think this question should be listed in the "Advanced Techniques" forum.