Professional Web Applications Themes

Newbie: Update all records of 1 table - Microsoft SQL / MS SQL Server

CASE is what you need. Here are three possibile solutions depending on whether and how you want to handle NULLs: If you don't have NULLs in the data: UPDATE TableName SET date1 = CASE WHEN date1 > date2 THEN date1 ELSE date2 END If you want the highest non-NULL value: UPDATE TableName SET date1 = CASE WHEN date1 > date2 THEN date1 ELSE COALESCE(date2,date1) END If you want NULL where one of the dates is NULL: UPDATE TableName SET date1 = CASE WHEN date1 > date2 OR date1 IS NULL THEN date1 ELSE date2 END -- David Portas ------------ Please ...

  1. #1

    Default Re: Newbie: Update all records of 1 table

    CASE is what you need. Here are three possibile solutions depending on
    whether and how you want to handle NULLs:

    If you don't have NULLs in the data:

    UPDATE TableName SET date1 =
    CASE
    WHEN date1 > date2
    THEN date1
    ELSE date2
    END

    If you want the highest non-NULL value:

    UPDATE TableName SET date1 =
    CASE
    WHEN date1 > date2
    THEN date1
    ELSE COALESCE(date2,date1)
    END

    If you want NULL where one of the dates is NULL:

    UPDATE TableName SET date1 =
    CASE
    WHEN date1 > date2 OR date1 IS NULL
    THEN date1
    ELSE date2
    END

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Olivier" <com> wrote in message
    news:093101c36889$4fb3af50$gbl... 


    David Guest

  2. #2

    Default Re: Newbie: Update all records of 1 table

    CORRECTION:

    In each case

    UPDATE TableName SET date3 =
    ....


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: Newbie: Update all records of 1 table

    I'm no guru, and still learning myself so this idea might not work.

    look up info on the "CASE" command.

    try somthing like

    update table set date3 = case when date1>date2 then date1 else date2
    end

    *not tested*

    HTH or gives you and idea how to do it
    Al

    On Fri, 22 Aug 2003 01:42:25 -0700, "Olivier" <com>
    wrote:
     

    Harag Guest

Similar Threads

  1. Replies: 0
    Last Post: April 15th, 01:30 PM
  2. Many form records to many table records
    By Anne in forum Microsoft Access
    Replies: 4
    Last Post: September 3rd, 02:12 PM
  3. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM
  4. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 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