Come play: "I can name that query in two lines"! 1

[RebLazer]

I have two tables: new_org_info and org_data. Both have a field called org_nbr.

new_org_info.org_nbr contains all null values. I need to copy every value of org_data.org_nbr into new_org_info.org_nbr (i.e. "update&quot whenever they share a common org_name.

What is the query?

Thanks,
RebLazer
Chicago, IL

 

[MeanGreen]

Try this:

update new_org_info
set org_nbr = org.org_nbr
from new_org_info n join org_data org
on n.org_name = org.org_name


Hope this helps.

 

[entaroadun]

The above will only work if org_name is unique in both tables. Is this true?

 

[MeanGreen]

Entaroadun,
Yes, you are correct. It will only update the org_nbr where the two databases have org_name equal.

Good Luck.

 

[entaroadun]

What I meant was:


If org_name is unique:

org_data
A 1
B 2
C 3

new_org
A <Null>
B <Null>
C <Null>

The update will work properly.


If org_name is not unique:

org_data
A 1
A 2
B 3

new_org
A <Null>
A <Null>
B <Null>

This will not work. You will get:

new_org
A 1
A 1
B 3

This happens because the update query takes the first value that matches the join criteria for the new value.

 

[bnagel2]

How much control do you have over the structure of the new_org_info table? If you could add a column that kept track of the number of occurences of each org_data name, then you could use that column to control your updates. Can you add a column to the table?

 

[toetag]

would something like:

update org_info set org_nbr where (select org_nbr from org_dat where org_nbr not in (select org_nbr from new_org_info))

work?

 

[toetag]

i messed up:

update new_org_info set org_nbr where (select org_nbr from org_data where org_nbr not in (select org_nbr from new_org_info))


I think that's correct now, insert it from org_data if not already in new_org_info. If i have my logic right.