[RESOLVED] sql server 2000 - help with insert from one table to another

[MMock]

I have a table called ldc_zip. It used to be just a look up table between ldc and zip. So there were three columns: ldc_zip_ik, ldc_ik and zip. zip was your usual USPS zip code, such as 49862. We also have a table called zip, which contains city-state-zipcode, etc. In the zipcode column of this table you would have 49862. There were actually three rows in the zip table with this zip code - Munising, Christmas and Forest Lake. We'll stick with that as an example.

We came to realize that we no longer wanted a zip code to be locked into a single ldc_ik. (LDC is Local Distribution Company, for natural gas). The zip table never had a key, but I recently (a day or two ago) gave it one. So it now has zip_ik. To the ldc_zip table I added column zip_ik and set it to NULL. This will "replace" zip code and I can use the key instead (but the zip code column will still hang around).

I hope you're still with me. This is my question. Where formerly I had one row in ldc_zip for 49862 which resolved to one ldc, I now want three. The zip_ik will index into the zip table, so ldc_zip needs a row with the zip_ik for Munising, Christmas and Forest Lake instead of just the 49862 row. Then they can each have their own LDC when that is the case. Is there a query I can run to populate ldc_zip with the other two rows? (Multiply this by all US cities - and that is why it needs to be a query). I also have to update the existing row to contain the zip_ik of one of the rows. It doesn't matter at the beginning if they all point the same ldc so I don't have to worry about that.

This is all so unclear I'm sure, and I don't expect to get any replies. But if you've managed to understand and can get me started with the queries, that would be great. Thanks.

[si_the_geek]

To start with you need to give zip_ik unique values, so set the Identity properties (you may need to remove the field and then re-add it).

You can then add zip_ik to ldc_zip, and run an Update to fill it - you won't be able to predict which of the values will be used, but it doesn't matter as you just want it to be unique.

The next step is one you should be wary of, because doing this could affect any existing queries that use the table... which is to add the extra records. To do it use an Insert with "zip_ik NOT IN(SELECT zip_ik FROM ldc_zip)", and a join to ldc_zip to get the values for the other columns.

[wild_bill]

Maybe something like this

set nocount on

declare @zip table (zip char(5), city varchar(100), zip_ik int)
insert into @zip(zip,city,zip_ik) values('49862','Munising',0)
insert into @zip(zip,city,zip_ik) values('49862','Christmas',1)
insert into @zip(zip,city,zip_ik) values('49862','Forest Lake',2)

declare @ldc_zip table (ldc_zip_ik int, ldc_ik int, zip char(5), zip_ik int)

insert into @ldc_zip (ldc_zip_ik,ldc_ik, zip, zip_ik) Values(0,0,'49862',null)

--update existing ldc_zip
update ldc
set zip_ik = zip.zip_ik
from @ldc_zip ldc
inner join @zip zip on ldc.zip = zip.zip

--insert missing cities
insert into @ldc_zip (ldc_zip_ik,ldc_ik, zip, zip_ik)
select	ldc.ldc_zip_ik,
		ldc.ldc_ik,
		ldc.zip,
		zip.zip_ik
from @ldc_zip ldc
inner join @zip zip on ldc.zip = zip.zip
where zip.zip_ik not in (select zip_ik from @ldc_zip)

select *
from @ldc_zip

[MMock]

To start with you need to give zip_ik unique values, so set the Identity properties (you may need to remove the field and then re-add it).

Check - did that already.

You can then add zip_ik to ldc_zip, and run an Update to fill it - you won't be able to predict which of the values will be used, but it doesn't matter as you just want it to be unique.

Check - zip_ik added to ldc_zip. It is NULL in most rows (except maybe the 1% I've been testing my code with and modifying). I don't agree that it doesn't matter what it is. It has to be the index into the zip table for the given zip code. So, if I have in ldc_zip the following (excuse my poor formatting):

ldc_zip_ik-------ldc_ik------zip----zip_ik
25043-------68--------46064-----8
34028-------68--------46064-----20448

8 will index into the zip table and grab Munising, 20448 will index into the zip table and grab Christmas, and I still need another row for Forest Lake.

The next step is one you should be wary of, because doing this could affect any existing queries that use the table... which is to add the extra records. To do it use an Insert with "zip_ik NOT IN(SELECT zip_ik FROM ldc_zip)", and a join to ldc_zip to get the values for the other columns.

I think I understand this but will wait for your comments on first two quotes. Thanks.

[MMock]

Maybe something like this...

Thanks, I may use some of this, but some of it I can't. I don't want to rebuild/modify the zip table except to add the PK (that's done)...but probably the insert of the missing cities I can use.

[si_the_geek]

For the second part you are filling one of the valid values (in fact it will do all of them, but repeatedly change the same record), and it probably doesn't matter to you which of the valid ones are used - but you may know different. What wild_bill posted looks fine to me.

The third part will add the rows for the other cities. What wild_bill posted is almost right, you should just remove ldc_zip_ik (from the field list and the Select list).

[MMock]

I actually had trouble constructing a select (for the insert) that would return any rows. I broke it down and ended up with this:

insert into ldc_zip(zip, zip_ik)
select zip_code, zip_ik
from zip 
where zip_ik not in (select zip_ik from ldc_zip where zip_ik is not null)

So now I have the correct # of rows in ldc_zip with the correct zip_ik for each row. What I want to do now is update the ldc_ik which is null for all the new rows since I left it out of the insert query.

I want to do something like this:

update ldc_zip 
set ldc_zip.ldc_ik = (select ldc_ik from ldc_zip where zip- table-A = zip-table-B which are the same table...)
where ldc_zip.ldc_ik is null

You can see my problem is I am trying to update rows in a table based on a select from the same table and don't know the syntax to differentiate one from the other (thought I could use an alias but my attempts failed).

Thanks for your continued help (if I'm not being too presumptuous!).

[si_the_geek]

When you want a subquery to 'link' to the parent query you need to use aliases for the tables - which you can then use to prefix the fields, ie:

update ldc_zip t1
set ldc_zip.ldc_ik = (select ldc_ik
                      from ldc_zip t2 
                      where t1.zip = t2.zip)
where ldc_zip.ldc_ik is null

[MMock]

Yes, that was very much like what I tried only I used a and b instead of t1 and t2. I just copied and tried your code and got:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't1'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

[si_the_geek]

ah... I've just remembered that you can't use an alias for the table to Update!

That is actually fine, as long as you use aliases for every other instance of the table:

update ldc_zip
set ldc_zip.ldc_ik = (select ldc_ik
                      from ldc_zip t2 
                      where ldc_zip.zip = t2.zip)
where ldc_zip.ldc_ik is null

[GaryMazzone]

I normally try this with inner joins

UPDATE ldc_zip SET
     ldc_zip.ldc_ik = t2.ldc_ik 
FROM ldc_zip t1 
INNER JOIN ldc_zip t2  On t1.zip = t2.zip
WHERE ldc_zip.ldc_ik is null

[MMock]

ah... I've just remembered that you can't use an alias for the table to Update!

ah...now I understand!

Awesome - with a little bit of tweaking the query worked. I got a subquery returned more than one value error (I was kind of expecting it, actually) and I also realized I was probably updating to null instead of updating the nulls to non-null, so this was the end result:

update ldc_zip
set ldc_zip.ldc_ik = (select top 1 ldc_ik
                      from ldc_zip t2 
                      where ldc_zip.zip = t2.zip and t2.ldc_ik is not null)
where ldc_zip.ldc_ik is null

Thanks very, very much!

[MMock]

I normally try this with inner joins

UPDATE ldc_zip SET
     ldc_zip.ldc_ik = t2.ldc_ik 
FROM ldc_zip t1 
INNER JOIN ldc_zip t2  On t1.zip = t2.zip
WHERE ldc_zip.ldc_ik is null

Thanks for another alternative Gary, but I am all set.