Post reply

Update a record based on another record

  • June 7, 2011 at 7:23 am

    #252276

    Hello all,

    I am looking for a way to update a record based on an already existing record.

    For instance I have TableA with columns Name,Location, Category, and Code.

    I have a record with the following values James, NJ , 1 , 6

    For each column respectively.

    I insert a new record with just the name Alex.

    I now want to update the Alex record with the same values as James. What would be the best way to accomplish this?

    In reality I have 25 columns that need to be updated and I am always creating new records and then updating them based on other existing records where several variations are possible. Using TSQL would really help me out time wise instead of going through the applicaiton GUI.

    Any thoughts are welcomed. Thanks.

    Keith

  • June 7, 2011 at 7:33 am

    #1335002

    It depends on your data. Maybe:

    INSERT INTO TableA (Name, Location, Category, Code)

    SELECT 'Alex', Location, Category, Code

    FROM TableA

    WHERE Name = 'James'

    If this does not work, post some sample data, in consumable format, along with the expected results.

  • June 7, 2011 at 7:34 am

    #1335003

    Assuming your table is called 'names'

    insert into names (name, location, category)

    select 'alex', n1.category, n1.location from

    names n1 where n1.name = 'james'

  • June 7, 2011 at 7:40 am

    #1335009

    ah.... Insert. (Hits self with palm to head) Thank you guys. I'm going to give that a try. Thats super helpful.

  • June 7, 2011 at 9:17 am

    #1335084

    That worked perfectly. The only hiccup was finding that the name needs to be upper case. Not sure how to work around case sensitivity.

    Otherwise that will easily save me 10 minutes per name.

  • June 7, 2011 at 10:51 pm

    #1335438

    you can use Upper()/Lower() function to overcome issues related case sensitive issues or look at collation...

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

  • June 8, 2011 at 12:35 pm

    #1335877

    Sriram.RM (6/7/2011)

    you can use Upper()/Lower() function to overcome issues related case sensitive issues or look at collation...

    those functions won't help you unless you are in a case sensitive collation.

    Try this

    select 1 where lower('ASDF') = UPPER('asdf')

    Simply using those inline will not produce the results you want.

    Try using collation to the same query and it will correctly identify upper case.

    select 1 where 'ASDF' = UPPER('asdf') collate SQL_Latin1_General_Cp437_BIN

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 9, 2011 at 12:54 pm

    #1336648

    Sean Lange (6/8/2011)

    Sriram.RM (6/7/2011)

    you can use Upper()/Lower() function to overcome issues related case sensitive issues or look at collation...

    those functions won't help you unless you are in a case sensitive collation.

    Try this

    select 1 where lower('ASDF') = UPPER('asdf')

    Simply using those inline will not produce the results you want.

    Try using collation to the same query and it will correctly identify upper case.

    select 1 where 'ASDF' = UPPER('asdf') collate SQL_Latin1_General_Cp437_BIN

    Using the function did work. I'll also try it with the collate statement. Oddly enough I looked at my server collation and it is SQL_Latin1_General_CP1_CI_AS. Shouldn't that make it case insensitive?

  • June 9, 2011 at 1:17 pm

    #1336674

    kwoznica (6/9/2011)

    Sean Lange (6/8/2011)

    Sriram.RM (6/7/2011)

    you can use Upper()/Lower() function to overcome issues related case sensitive issues or look at collation...

    those functions won't help you unless you are in a case sensitive collation.

    Try this

    select 1 where lower('ASDF') = UPPER('asdf')

    Simply using those inline will not produce the results you want.

    Try using collation to the same query and it will correctly identify upper case.

    select 1 where 'ASDF' = UPPER('asdf') collate SQL_Latin1_General_Cp437_BIN

    Using the function did work. I'll also try it with the collate statement. Oddly enough I looked at my server collation and it is SQL_Latin1_General_CP1_CI_AS. Shouldn't that make it case insensitive?

    Yes that is case insensitive. If the first query I posted returned 1 record then that means it found the match and in the case of this example that would not produce the desired results.

    I tweaked it slightly to provide more clear results.

    select case when lower('ASDF') = UPPER('asdf') then 'case insensitive' else 'case sensitive' end

    select case when lower('ASDF') = UPPER('asdf') collate SQL_Latin1_General_Cp437_BIN then 'case insensitive' else 'case sensitive' end

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 9, 2011 at 1:18 pm

    #1336675

    fyi, you can run SELECT * FROM fn_helpcollations() to view all the available collations.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 15, 2011 at 8:19 am

    #1339007

    Sean Lange (6/9/2011)

    fyi, you can run SELECT * FROM fn_helpcollations() to view all the available collations.

    Thanks Sean. I ran it and saw 2397 different collations. I looked at the database properties for our ERP and saw it is using Latin1_General_CS_AS, However my database server collation is SQL_Latin1_General_CP1_CI_AS.

    When I insall SQL server I always use the SQL_Latin1_General_CP1_CI_AS, collation. I am guessing that the production database object has always had this Latin1_General_CS_AS collation. Can that be changed and if so what are potential ramifications?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply