Professional Web Applications Themes

Update a column ... - Microsoft SQL / MS SQL Server

Try this Update a set a.C6 =0 from TableName a Inner join TableName b on ( a.C2 = b.C2 and a.C3 = b.C3 and a.c4 = b.c4) where a.C6 < b.c6 HTH, Srinivas Sampangi   last column is the  and C4 are considered  record is kept but  to zero. For  the same among  kept, but values of  query to perform ...

  1. #1

    Default Update a column ...

    Try this

    Update a
    set a.C6 =0
    from TableName a
    Inner join TableName b on ( a.C2 = b.C2 and
    a.C3 = b.C3 and a.c4 = b.c4)
    where a.C6 < b.c6

    HTH,
    Srinivas Sampangi
     
    last column is the 
    and C4 are considered 
    record is kept but 
    to zero. For 
    the same among 
    kept, but values of 
    query to perform 
    sampangi Guest

  2. #2

    Default Re: Update a column ...

    Thanks for the replay. I have only one table. I think there are two tables
    in your quey?

    "sampangi" <com> wrote in message
    news:038201c35e0d$7eb765c0$gbl... 
    > last column is the 
    > and C4 are considered 
    > record is kept but 
    > to zero. For 
    > the same among 
    > kept, but values of 
    > query to perform [/ref]


    Polaris Guest

  3. #3

    Default Re: Update a column ...

    It is the self join on the same table.Replace 'TableName" with your actual
    table name in the given query.a and b are Aliases for the same table.

    HTH,
    Srinivas Sampangi

    "Polaris" <com> wrote in message
    news:phx.gbl... 
    > > last column is the 
    > > and C4 are considered 
    > > record is kept but 
    > > to zero. For 
    > > the same among 
    > > kept, but values of 
    > > query to perform [/ref]
    >
    >[/ref]


    sampangi Guest

  4. #4

    Default Re: Update a column ...

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    And rows are NOT records -- huge differences! The phrase "biggest
    record [sic]" is not defined -- I think you meant to preserve the row
    with the highest value in column C6. Here is a guess

    CREATE VIEW Dups (c2, c3, c4, max_c6)
    SELECT c2, c3, c4, MAX(c6)
    FROM Foobar
    GROUP BY c2, c3, c4;

    UPDATE Foobar
    SET c6
    = (SELECT CASE WHEN Foobar.c6 = D1.max_c6
    THEN F1.max_c6 ELSE 0 END
    FROM Dups AS D1
    WHERE Foobar.c2 = D1.c2
    AND Foobar.c3 = D1.c3
    AND Foobar.c4 = D1.c4);

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Can't insert or update password column
    By JasonCF in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 15th, 12:56 AM
  2. Update portion of a string column
    By Yves Glodt in forum IBM DB2
    Replies: 2
    Last Post: September 12th, 06:04 PM
  3. How to update a column ?
    By Polaris in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 5th, 06:01 PM
  4. Dynamically set the column name for IF UPDATE ( column ) in trigger
    By Jay in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 17th, 05:30 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