Professional Web Applications Themes

Default Value in a Table on SQL7 - Microsoft SQL / MS SQL Server

You don't really need to have a column for this, as you can always determine this on the fly, within your SELECT statement. For example: SELECT Email, CASE WHEN LEN(Email) > 0 THEN 1 ELSE NULL END AS EmailExists FROM SomeTable WHERE CustID = <Something> You could also have a computed column if you wish. See SQL Server Books Online for more information. -- HTH, Vyas, MVP (SQL Server) [url]http://vyaskn.tripod.com/[/url] What hardware is your SQL Server running on? [url]http://vyaskn.tripod.com/poll.htm[/url] "David" <db_1984> wrote in message news:033b01c33f37$cf8818e0$a101280aphx.gbl... How do I change the default value for a column name from <NULL> to 1 ...

  1. #1

    Default Re: Default Value in a Table on SQL7

    You don't really need to have a column for this, as you can always determine
    this on the fly, within your SELECT statement. For example:

    SELECT Email, CASE WHEN LEN(Email) > 0 THEN 1 ELSE NULL END AS EmailExists
    FROM SomeTable
    WHERE CustID = <Something>

    You could also have a computed column if you wish. See SQL Server Books
    Online for more information.

    --
    HTH,
    Vyas, MVP (SQL Server)
    [url]http://vyaskn.tripod.com/[/url]
    What hardware is your SQL Server running on?
    [url]http://vyaskn.tripod.com/poll.htm[/url]




    "David" <db_1984> wrote in message
    news:033b01c33f37$cf8818e0$a101280aphx.gbl...
    How do I change the default value for a column name from
    <NULL> to 1

    I need this field name to always to be updated to display 1

    Example:

    When field name email_adrress is present I need to change
    the value for email_indic from <NULL> to 1

    Thanks
    David


    Narayana Vyas Kondreddi Guest

  2. #2

    Default Re: Default Value in a Table on SQL7

    Here's a sample that might help.



    CREATE TABLE blat
    (
    a INT,
    email_indic INT
    )
    INSERT blat(a, email_indic) VALUES(1, 0)
    INSERT blat(a, email_indic) VALUES(1, NULL)
    INSERT blat(a, email_indic) VALUES(1, 1)
    GO

    -- Okay, let's get rid of the NULLs that already exist

    UPDATE blat SET email_indic=1 WHERE email_indic IS NULL
    GO

    -- Now let's add a default constraint

    ALTER TABLE blat ADD CONSTRAINT defei DEFAULT 1 FOR email_indic
    GO

    -- now let's check that the constraint works

    INSERT blat(a) VALUES(1)
    SELECT * FROM blat

    DROP TABLE blat



    However, I agree with Vyas, who explained that you don't need to store this
    in the table, since it's deterministic \

    In other words, you can check for that state in the query, and it's more
    reliable than storing the data.

    You aren't really controlling the two columns as one, e.g. a user could
    update one or the other and, short of a killer trigger, you couldn't
    guarantee the state to be consistent... so you can't really be sure that
    just because the email_indic column has a 1 you're guaranteed to have a
    valid e-mail entered...





    "David" <db_1984> wrote in message
    news:033b01c33f37$cf8818e0$a101280aphx.gbl...
    > How do I change the default value for a column name from
    > <NULL> to 1
    >
    > I need this field name to always to be updated to display 1
    >
    > Example:
    >
    > When field name email_adrress is present I need to change
    > the value for email_indic from <NULL> to 1
    >
    > Thanks
    > David

    Aaron Bertrand - MVP Guest

Similar Threads

  1. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  2. How do I set a default value once a table is built ?
    By Derek Clarkson in forum IBM DB2
    Replies: 1
    Last Post: September 12th, 03:39 PM
  3. Replies: 1
    Last Post: July 31st, 07:47 PM
  4. Table Default Values
    By Michael Volz in forum Microsoft Access
    Replies: 2
    Last Post: July 31st, 02:04 PM
  5. SQL7 Views and INSERT
    By Mike Brooks in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 12th, 08:25 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