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

    new update query?

    newchickinCF Level 1
      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?
            fober1 Level 1

            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.