Professional Web Applications Themes

update and having clause?? - Microsoft SQL / MS SQL Server

does sql allow a having clause within and update statement? i have the following update statement that shows what i want to do - but i get a syntax error near the having clause - im thinking that sql must not allow this in an update clause. any ideas on another way to do this?? UPDATE tpayment SET scheduled_method_type_id = 3, status_id = 3 WHERE contract_id = 28937 and payment_no = 1 and status_id = 3 HAVING count(payment_no) = 1 ie, i only want to perform this update if the count of payment number 1s is equal to 1. in ...

  1. #1

    Default update and having clause??

    does sql allow a having clause within and update
    statement? i have the following update statement that
    shows what i want to do - but i get a syntax error near
    the having clause - im thinking that sql must not allow
    this in an update clause. any ideas on another way to do
    this??


    UPDATE tpayment
    SET scheduled_method_type_id = 3, status_id = 3
    WHERE contract_id = 28937 and payment_no = 1 and
    status_id = 3
    HAVING count(payment_no) = 1

    ie, i only want to perform this update if the count of
    payment number 1s is equal to 1. in other words there are
    times when my system will have more than one payment
    number 1s and for these cases i dont want to update.
    JT Guest

  2. #2

    Default Re: update and having clause??

    JT,

    How about this?

    UPDATE tpayment
    SET scheduled_method_type_id = 3, status_id = 3
    WHERE contract_id = 28937 and payment_no = 1 and
    status_id = 3
    AND
    (SELECT count(*)
    FROM tpayment
    WHERE contract_id = 28937 and payment_no = 1 and
    status_id = 3) = 1

    Or, just for fun, this: :-)

    BEGIN TRANSACTION
    UPDATE tpayment
    SET scheduled_method_type_id = 3, status_id = 3
    WHERE contract_id = 28937 and payment_no = 1 and
    status_id = 3

    IF ROWCOUNT != 1
    ROLLBACK TRANSACTION
    ELSE
    COMMIT TRANSACTION


    Russell Fields

    "JT" <jeffreylsppinc.net> wrote in message
    news:0ab301c340d0$177e6340$a001280aphx.gbl...
    > does sql allow a having clause within and update
    > statement? i have the following update statement that
    > shows what i want to do - but i get a syntax error near
    > the having clause - im thinking that sql must not allow
    > this in an update clause. any ideas on another way to do
    > this??
    >
    >
    > UPDATE tpayment
    > SET scheduled_method_type_id = 3, status_id = 3
    > WHERE contract_id = 28937 and payment_no = 1 and
    > status_id = 3
    > HAVING count(payment_no) = 1
    >
    > ie, i only want to perform this update if the count of
    > payment number 1s is equal to 1. in other words there are
    > times when my system will have more than one payment
    > number 1s and for these cases i dont want to update.

    Russell Fields Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  3. help with GROUP BY clause?
    By Nathon Jones in forum Dreamweaver AppDev
    Replies: 10
    Last Post: February 25th, 07:06 PM
  4. Using variable in From clause
    By Glenn Stein in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 08:45 PM
  5. IN clause with 2 fields
    By Gustavo Pizzini Becker in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:07 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139