Changing column values to be all the same

Question

Changing column values to be all the same

dale_keller

SSChasing Mays

Points: 653
More actions
December 29, 2008 at 10:08 am

#94862

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?

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

dale_keller SSChasing Mays Points: 653 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions