Professional Web Applications Themes

Updating a column with another column - Microsoft SQL / MS SQL Server

Dan, Assuming you already created 'common_name' column.... UPDATE dbo.customers SET common_name = contact_fname+SPACE(1)+contact_lname WHERE <condition> -- Dinesh. SQL Server FAQ at http://www.tkdinesh.com "Dan Marth" <net> wrote in message news:phx.gbl...  contact_fname  same ...

  1. #1

    Default Re: Updating a column with another column

    Dan,

    Assuming you already created 'common_name' column....

    UPDATE dbo.customers
    SET common_name = contact_fname+SPACE(1)+contact_lname
    WHERE <condition>


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dan Marth" <net> wrote in message
    news:phx.gbl... 
    contact_fname 
    same 


    Dinesh.T.K Guest

  2. #2

    Default Re: Updating a column with another column

    WHERE <condition> is the problem, I can udate one record at a time by using
    where username='domain\danm' but domain\danm won't work for the next record.
    I need username=variable


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    > contact_fname 
    > same 
    >
    >[/ref]


    Dan Guest

  3. #3

    Default Re: Updating a column with another column

    Dan,
     [/ref][/ref]
    be [/ref][/ref]

    Your post is unclear.I assumed, regardless of the WHERE condition, you want
    the new column to be the combination of firstname and lastname.Can you
    mention how that requirement is going to change depending on username?If you
    can provide the table DDL and the sample output, it would be great.If the
    WHERE is dynamic, then you may need to use dynamic sql.Please refer the
    below article for pros and cons on that:

    The Curse and Blessings of Dynamic SQL
    http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_update


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dan Marth" <net> wrote in message
    news:phx.gbl... 
    using 
    record. [/ref][/ref]
    be 
    > > contact_fname 
    > > same 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  4. #4

    Default Re: Updating a column with another column

    Thanks for all your help but I think the easiest way is to export the whole
    table to a spread sheet, delete the table, then import the spread sheet but
    change the mapping to "contact_fname" & " " & "contact_lname" into the
    common_name column using DTS.

    Thanks again,
    Dan

    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:%23fzQ$phx.gbl... [/ref]
    > be [/ref]
    >
    > Your post is unclear.I assumed, regardless of the WHERE condition, you[/ref]
    want 
    you 
    > using 
    > record. [/ref][/ref]
    would [/ref][/ref]
    the [/ref][/ref]
    where 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  5. #5

    Default Re: Updating a column with another column

    Just to clear up what I was trying to do through a SQL query. How does it
    know to update the correct common_name field for that user without some kind
    of where statement? I don't want John Smith's common_name to be Mike Jones.
    It somehow needs to find the John Smith record and take his contact_fname
    and contact_lname, merge it with a space and put John Smith in the
    common_name field. I have no idea how to do that without an export and
    import. If you have a better idea let me know.

    Thanks,
    Dan

    "Dan Marth" <net> wrote in message
    news:phx.gbl... 
    whole 
    but [/ref][/ref]
    would 
    > >
    > > Your post is unclear.I assumed, regardless of the WHERE condition, you[/ref]
    > want 
    > you [/ref]
    the 
    > > using 
    > > record. [/ref]
    > would [/ref]
    > the [/ref]
    > where [/ref][/ref]
    record) 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  6. #6

    Default Re: Updating a column with another column

    Dan,

    Try this code and see the output:

    /*****************************/
    SET NOCOUNT ON
    CREATE TABLE newnames
    (
    fname VARCHAR(20),
    lname VARCHAR(20),
    fullname VARCHAR(40) null
    )
    GO
    INSERT newnames(fname,lname) VALUES('John','Doe')
    INSERT newnames(fname,lname) VALUES('Jane','Doe')
    INSERT newnames(fname,lname) VALUES('John','Smith')
    INSERT newnames(fname,lname) VALUES('Mike','Jones')
    GO
    UPDATE newnames
    SET fullname=fname+SPACE(1)+lname
    GO
    SELECT * FROM newnames
    GO
    DROP TABLE newnames
    GO
    SET NOCOUNT OFF
    /*****************************/


    output
    ----

    fname lname fullname
    -------------------- -------------------- --------------
    John Doe John Doe
    Jane Doe Jane Doe
    John Smith John Smith
    Mike Jones Mike Jones



    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dan Marth" <net> wrote in message
    news:phx.gbl... 
    kind 
    Jones. 
    > whole 
    > but [/ref]
    > would 
    > > want [/ref][/ref]
    you [/ref][/ref]
    username?If [/ref]
    > the [/ref][/ref]
    the [/ref][/ref]
    by [/ref][/ref]
     [/ref][/ref]
    record 
    > > where [/ref]
    > record) 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  7. #7

    Default Re: Updating a column with another column

    But would I need to type out the statement below for all 3000 customers in
    the table?

    INSERT newnames(fname,lname) VALUES('John','Doe')
    INSERT newnames(fname,lname) VALUES('Jane','Doe')
    INSERT newnames(fname,lname) VALUES('John','Smith')
    INSERT newnames(fname,lname) VALUES('Mike','Jones')

    I think this would only update 4 users.


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... [/ref]
    it 
    > kind 
    > Jones. [/ref]
    contact_fname 
    > > whole [/ref][/ref]
    sheet 
    > > would [/ref][/ref]
    you [/ref]
    > you [/ref]
    > username?If [/ref][/ref]
    great.If [/ref]
    > the [/ref]
    > by [/ref][/ref]
    next [/ref][/ref]
    which [/ref]
    > record [/ref][/ref]
    contact_lname 
    > > record) 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  8. #8

    Default Re: Updating a column with another column

    Dan,

    No need.That was an example since Iam unaware about your exact table
    structure.The point is, you dont need to mention the WHERE clause since you
    want all the individual firstname and lastname to be combined.So just do:

    UPDATE dbo.customers
    SET common_name = contact_fname+SPACE(1)+contact_lname

    This command would populate the common_name column for all 3000 customers
    with the respective combo of contact_fname and contact_lname.In fact, before
    doing the update , convince yourself that the output is correct by doing:

    SELECT contact_fname+SPACE(1)+contact_lname
    FROM dbo.customers


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dan Marth" <net> wrote in message
    news:uMwPqy%phx.gbl... [/ref][/ref]
    does [/ref][/ref]
    some 
    > > Jones. [/ref]
    > contact_fname [/ref][/ref]
    and [/ref][/ref]
    the [/ref]
    > sheet [/ref][/ref]
    the [/ref][/ref]
    which [/ref]
    > you [/ref][/ref]
    lastname.Can 
    > > username?If [/ref]
    > great.If [/ref][/ref]
    refer [/ref][/ref]
    time [/ref]
    > next [/ref][/ref]
    message [/ref]
    > which [/ref][/ref]

    > > record [/ref]
    > contact_lname 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  9. #9

    Default Re: Updating a column with another column

    It worked, I never would have guessed it would be that easy!!! I didn't
    think it would do them in the correct order.

    Problem now is, I noticed that some first and last names have spaces before
    or after there name. Which caused an extra space or two between the first
    and last name in the common_name field.

    If there is away to take the spaces out of the contact_fname and
    contact_lname before running the update, please let me know. Those spaces
    will will also cause other problems with lookups if I don't get rid of them.

    Thanks for all the help,
    Dan
    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    you 
    before [/ref]
    in [/ref]
    > does [/ref]
    > some [/ref][/ref]
    Mike 
    > > contact_fname [/ref]
    > and [/ref]
    > the 
    > > sheet [/ref]
    > the [/ref]
    > which [/ref][/ref]
    condition, [/ref]
    > lastname.Can 
    > > great.If [/ref]
    > refer [/ref]
    > time [/ref][/ref]
    the [/ref]
    > message 
    > > which [/ref][/ref]
    have [/ref][/ref]
    every 
    > > contact_lname [/ref][/ref]
    each 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  10. #10

    Default Re: Updating a column with another column

    Dan,

    Try

    UPDATE dbo.customers
    SET common_name = REPLACE(contact_fname,'
    ','')+SPACE(1)+REPLACE(contact_lname,' ','')

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dan Marth" <net> wrote in message
    news:phx.gbl... 
    before 
    them. 
    > you [/ref]
    do: [/ref]
    customers 
    > before [/ref]
    doing: [/ref][/ref]
    customers 
    > > does 
    > > some [/ref]
    > Mike [/ref][/ref]
    export [/ref][/ref]
    export [/ref][/ref]
    spread [/ref][/ref]
    into [/ref][/ref]
    message 
    > > which [/ref]
    > condition, 
    > > lastname.Can 
    > > refer [/ref][/ref]
    a [/ref]
    > the 
    > > message [/ref][/ref]
    tables, [/ref]
    > have [/ref]
    > every [/ref]
    > each 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  11. #11

    Default Re: Updating a column with another column

    Excelent, that worked great!!! Thanks for all the help, I also used the
    below statement to fix the spaces in contact_fname and contact_lname.

    UPDATE dbo.customers SET contact_fname=REPLACE(contact_fname,' ','')
    UPDATE dbo.customers SET contact_lname=REPLACE(contact_lname,' ','')

    Thanks again,
    Dan
    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    > before [/ref]
    first [/ref]
    spaces 
    > them. [/ref][/ref]
    since [/ref]
    > do: [/ref]
    > customers 
    > > before [/ref]
    > doing: [/ref]
    > customers [/ref][/ref]
    How [/ref][/ref]
    without 
    > > Mike [/ref][/ref]
    the [/ref]
    > export [/ref]
    > export [/ref]
    > spread [/ref]
    > into [/ref]
    > message [/ref][/ref]
    tables, 
    > > condition, [/ref][/ref]
    sql.Please [/ref][/ref]
    at [/ref][/ref]
    for [/ref]
    > tables, 
    > > have 
    > > every 
    > > each 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

Similar Threads

  1. Column chart need column to exceed axis maximum value
    By zanedev in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: May 8th, 04:26 PM
  2. Updating data in a column using it's own value
    By drmaves in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: May 5th, 06:52 PM
  3. Move bound column to right of dynamic column in datagrid?
    By John E. in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: March 28th, 01:29 PM
  4. Bound Column or Template Column (w dAdapater?) in DataGrid
    By Ravichandran Mahalingam in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 9th, 03:30 PM
  5. Replies: 0
    Last Post: July 7th, 08:32 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