Changing column values to be all the same

  • Hi,

    I have two tables:

    Table 1 has two columns which I'll call TBL_1_COL_1 and TBL_1_COL_2.

    Table 2 also has two columns which I'll call TBL_2_COL_1 and TBL_2_COL_2.

    As an example the values in table 1 look like this:

    TBL_1_COL_1 TBL_1_COL_2

    ========== ==========

    91129 236747

    91129 401046

    91129 446609

    91129 446610

    Table 2 has values like this:

    TBL_2_COL_1 TBL_2_COL_2

    ========== =========

    236747 F

    401046 F

    446609 M

    446610 M

    I inner join TBL_1_COL_2 with TBL_2_COL_1 so the results will look like this:

    TBL_1_COL_1 TBL_2_COL_1 TBL_2_COL_2

    ----------- ---------- -------------

    91129 236747 F

    91129 401046 F

    91129 446609 M

    91129 446610 M

    I want to know if there is a way to set TBL_2_COL_2 to all the same values based on a value in TBL_2_COL_2. For example if any value in TBL_2_COL_2 equals 'F' I want to set all the remaining rows to 'F' where the value in TBL_1_COL_1 is the same (eg. 91129)? So the end result would look like this:

    TBL_1_COL_1 TBL_2_COL_1 TBL_2_COL_2

    ----------- ---------- -------------

    91129 236747 F

    91129 401046 F

    91129 446609 F

    91129 446610 F

    Any Ideas?

  • It is better to write a script that creates the tables and inserts data into the table instead of drawing the table and the columns' values. Pleas have a look at the script that I wrote (which includes the creation of the table and insert statements to make it easier to check it) and also check out the URL at my signature to a post that shows how to ask questions.

    As for your question - The script bellow has a query that does what you asked

    create table tbl_1 (col_1 int, col_2 int)

    go

    insert into tbl_1 (col_1, col_2)

    select 91129, 236747

    union select 91129, 401046

    union select 91129, 446609

    union select 91129, 446610

    union select 1, 1

    go

    create table tbl_2 (col_1 int, col_2 char(1))

    go

    insert into tbl_2 (col_1, col_2)

    select 236747, 'F'

    union select 401046, 'F'

    union select 446609, 'M'

    union select 446610, 'M'

    union select 1, 'm'

    go

    with CheckMinCol2 as

    (select tbl_1.col_1, min(tbl_2.col_2) as col_2

    from tbl_1 inner join tbl_2 on tbl_1.col_2 = tbl_2.col_1

    group by tbl_1.col_1)

    select CheckMinCol2.col_1, tbl_2.Col_1, CheckMinCol2.col_2

    from tbl_1 inner join tbl_2 on tbl_1.col_2 = tbl_2.col_1

    inner join CheckMinCol2 on tbl_1.col_1 = CheckMinCol2.col_1

    go

    drop table tbl_1

    go

    drop table tbl_2

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the code snippet - it works really well. Also, thanks for the link to 'Best Practises' when posting a question. Very helpful and something I'll remember next time.

  • Good form, Adi. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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