Professional Web Applications Themes

Is it possible to do this using TSQL instead of cursor ?? - Microsoft SQL / MS SQL Server

I have a Tagdataminutely table with columns TagKey, datetime, Value, Quality and Rev. TagKey is not a primary key in this table, in fact this table has no primary key at all. When an Update statement is issued on Value or Quality or Rev fields, the new value specified in update statement replaces old value but rev and quality field always retain their old values. For this I have used deleted table and an Update function inside trigger on that table and update Tagdataminutely table with old values... If update(value) or update(rev) or update(quality) Begin update Tagdataminutely set Quality = ...

  1. #1

    Default Is it possible to do this using TSQL instead of cursor ??

    I have a Tagdataminutely table with columns TagKey,
    datetime, Value, Quality and Rev. TagKey is not a primary
    key in this table, in fact this table has no primary key
    at all. When an Update statement is issued on Value or
    Quality or Rev fields, the new value specified in update
    statement replaces old value but rev and quality field
    always retain their old values.

    For this I have used deleted table and an Update function
    inside trigger on that table and update Tagdataminutely
    table with old values...

    If update(value) or update(rev) or update(quality)
    Begin
    update Tagdataminutely
    set Quality = (Select deleted.Quality from
    deleted where deleted.[datetime] = tagdataminutely.
    [datetime] and deleted.Tagkey = tagdataminutely.Tagkey),
    Revlevel = (Select deleted.revlevel from deleted
    where deleted.[datetime] = tagdataminutely.[datetime] and
    deleted.Tagkey = tagdataminutely.Tagkey ) where [datetime]
    >= sdtMinStartDate and [datetime] <= sdtMaxEndDate
    End

    The error message I get is :

    Subquery returned more than 1 value. This is not permitted
    when the subquery follows =, !=, <, <= , >, >= or when the
    subquery is used as an expression.
    The statement has been terminated.

    Can I limit the returned resultset from subquery to a
    single value for all records in deleted table one by
    one..???

    OR

    Do I have to loop thru cursor and update the table for
    each records one by one, which is very slow as the updates
    on this table could happen in the magnitude of 1000s in
    few minutes....

    Help is very much appreciated.

    Thanks

    Ricky


    Ricky Guest

  2. #2

    Default Re: Is it possible to do this using TSQL instead of cursor ??

    >Can I limit the returned resultset from subquery to a
    >single value for all records in deleted table one by
    >one..???
    Yes, but its upto you and what your application needed.
    Because you know your query better. for example you first sub-query returns
    quality column's values it may fetch 2 different rows with quality column's
    values as 2 or 3 in that case its upto you to know which is the correct row
    to be picked up.
    if you are sure these multiple rows are always going return the same values
    then you can include distinct clause to eliminate mulitple rows.

    --
    -Vishal
    "Ricky" <ricky.arorametc.state.mn.us> wrote in message
    news:794f01c344ab$c4e90dd0$a401280aphx.gbl...
    > I have a Tagdataminutely table with columns TagKey,
    > datetime, Value, Quality and Rev. TagKey is not a primary
    > key in this table, in fact this table has no primary key
    > at all. When an Update statement is issued on Value or
    > Quality or Rev fields, the new value specified in update
    > statement replaces old value but rev and quality field
    > always retain their old values.
    >
    > For this I have used deleted table and an Update function
    > inside trigger on that table and update Tagdataminutely
    > table with old values...
    >
    > If update(value) or update(rev) or update(quality)
    > Begin
    > update Tagdataminutely
    > set Quality = (Select deleted.Quality from
    > deleted where deleted.[datetime] = tagdataminutely.
    > [datetime] and deleted.Tagkey = tagdataminutely.Tagkey),
    > Revlevel = (Select deleted.revlevel from deleted
    > where deleted.[datetime] = tagdataminutely.[datetime] and
    > deleted.Tagkey = tagdataminutely.Tagkey ) where [datetime]
    > >= sdtMinStartDate and [datetime] <= sdtMaxEndDate
    > End
    >
    > The error message I get is :
    >
    > Subquery returned more than 1 value. This is not permitted
    > when the subquery follows =, !=, <, <= , >, >= or when the
    > subquery is used as an expression.
    > The statement has been terminated.
    >
    > Can I limit the returned resultset from subquery to a
    > single value for all records in deleted table one by
    > one..???
    >
    > OR
    >
    > Do I have to loop thru cursor and update the table for
    > each records one by one, which is very slow as the updates
    > on this table could happen in the magnitude of 1000s in
    > few minutes....
    >
    > Help is very much appreciated.
    >
    > Thanks
    >
    > Ricky
    >
    >

    Vishal Parkar Guest

Similar Threads

  1. How you convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 23rd, 01:25 PM
  2. How u convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 22nd, 09:13 PM
  3. Using web services inside TSQL
    By SriSamp in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 24th, 11:43 AM
  4. IIF/Format equivalent in TSQL
    By andi in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 05:11 PM
  5. Please improve this TSQL algorithm ..
    By Krist Lioe in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 08:18 AM

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