Professional Web Applications Themes

Updating SELECTED rows - Microsoft SQL / MS SQL Server

Is there a way to UPDATE a specific column when records are selected? For example, I sometime use this: SELECT TOP 10 * FROM tbl WHERE col_id IN (Rnd_list_of_value) How can I instantly update column (col_select_count) with +1 for all selected values? can I do it on the same statment via trigger of some sort? without a 2nd update query? Thanks, == Jas....

  1. #1

    Default Updating SELECTED rows

    Is there a way to UPDATE a specific column when records are selected?

    For example, I sometime use this:

    SELECT TOP 10 * FROM tbl WHERE col_id IN (Rnd_list_of_value)

    How can I instantly update column (col_select_count) with +1 for all
    selected values? can I do it on the same statment via trigger of some sort?
    without a 2nd update query?

    Thanks,

    ==
    Jas.


    Jason Guest

  2. #2

    Default Re: Updating SELECTED rows

    Jason Davis wrote: 

    No. Triggers are only fired when some sort of data modification is done
    (insert, update, delete). Select statements neither modify data nor fire
    triggers.You will need an UPDATE statement:

    SET ROWCOUNT = 10
    UPDATE tbl
    SET col_select_count = col_select_count + 1
    WHERE col_id IN (Rnd_list_of_value)
    SET ROWCOUNT = 0

    HTH,
    Bob Barrows


    Bob Guest

  3. #3

    Default Re: Updating SELECTED rows

    Hello Jason !

    No Triggers only refer to DML Statement (UPDATE;INSERT,DELETE). The only
    thing you could handle the counting of the selected rows, is to query the
    database through a SP , in the SP you could easily chnage the counter a the
    Selected rowss before sending the data to the client application.

    HTH, Jens Süßmeyer.



    Jens Guest

  4. #4

    Default Re: Updating SELECTED rows

    Jason,

    You can't make the SELECT query update rows from
    your table, but you can make an UPDATE query select
    rows from your table. (You just have to stand on your
    head when you think about the problem...):

    create table T (
    i int primary key,
    c int
    )
    go

    -- insert sample data
    insert into T
    select top 40 OrderID, 0
    from Northwind..Orders
    where customerID < 'L'
    go

    create trigger T_upd on T
    for update as
    select * from deleted
    go

    -- you will see the rows that are being updated
    update T set
    c = c + 1
    where i%11 = 8
    go

    -- check that they were updated
    select * from T
    go

    drop table T

    -- Steve Kass
    -- Drew University
    -- Ref: DD983543-3CDE-4AD0-8293-3AE42E0956ED


    Jason Davis wrote:
     

    Steve Guest

Similar Threads

  1. Getting selected rows out of an access database
    By badLarry in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 7th, 04:07 PM
  2. Getting a list of ID's from Selected Rows
    By Thomasa Gregg in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 21st, 09:03 PM
  3. how to return all rows selected in subroutine?
    By Shaunn Johnson in forum PERL Beginners
    Replies: 1
    Last Post: August 29th, 08:33 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