Update a table from another table

[elsolo21]

I have an existing table with about 10 attributes and columns for 5 years of data. I want to take another table with similar attributes but only one year of data and update the first table. This one year is for a new year which does not exist in the first table. For example:

Table 1
attr 1
attr 2
attr 3
...
2005
2006
2007
2008
2009

Table 2
attr 1
attr 2
attr 3
...
2010

The problem is that the combinations of the attributes will be slightly different in both tables so I cannot join them (equi, left or right) at all.

This might be an easy thing but I just can't figure it out.

Any ideas?
thanks,
-Eric

 

[randy700]

The structure you provide is vague, but it doesn't sound like your table is normalized.
However, the procedure you're looking for would be an append query.


Randy

 

[elsolo21]

Thanks for the reply. You're right, the structure is horrible but there are so many things built off it that it would be a huge project to change it.

I thought of an append query but then I would just have double the records and the attributes would be doubled in some places. One thing that I didn't originally post that might help is that the data fields are summarized by the attributes so each combination of attributes appear only once. I used to do all this in SAS with an update step but that is no longer an option.

 

[randy700]

Can you provide some sample data?


Randy

 

[elsolo21]

Lets see if this helps:

Existing table 1:

Company State Line 2005 2006 2007 2008 2009
A NY P 100 200 200 300 400
B PA L 100 200 200 300 400
A PA Q 200 230 300 400 400

New Table 2:
Company State Line 2010
A NY Q 500
B PA L 500


So what I'd be looking to get is:
Company State Line 2005 2006 2007 2008 2009 2010
A NY P 100 200 200 300 400
B PA L 100 200 200 300 400 500
A PA Q 200 230 300 400 400
A NY Q 500

For table 2, the first record, the three atributes already exist in that combination for table 1 so I just want to 'tack on' the 2010 value but the 2nd record is a new combination so that would just be a new record with just a 2010 value and the 2005-2009 values would be null.

thanks,
-Eric

 

[MajP]

You can do this with 2 queries

qryUpdateExisting:
UPDATE Table1 INNER JOIN Table2 ON (Table1.Company = Table2.Company) AND (Table1.State = Table2.State) AND (Table1.Line = Table2.Line) SET Table1.[2010] = [table2].[2010];

qryAddNewRecordS:
INSERT INTO Table1 ( 2010, Company, State, Line )
SELECT Table2.[2010], Table2.Company, Table2.State, Table2.Line
FROM Table1 RIGHT JOIN Table2 ON (Table1.Company = Table2.Company) AND (Table1.State = Table2.State) AND (Table1.Line = Table2.Line)
WHERE Table1.Company Is Null;


As mentioned, I would recommend strongly that you fix this database design. In the long wrong this structure is going to make simple things very hard to do, and make data integrity hard to enforce.

 

[PHV]

Why two queries ?

UPDATE Table1 AS O RIGHT JOIN Table2 AS N ON O.Company = N.Company AND O.State = N.State AND O.Line = N.Line
SET O.Company = N.Company, O.State = N.State, O.Line = N.Line, O.[2010] = N.[2010]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[elsolo21]

Maybe this is just my lack of SQL experience but how would 'O.[2010] = N.[2010]' work if 2010 doesn't exist in Table1?

Also, I don't think I see how this would work when Table2 brings in a new combination of Company,State and line with the right join
thanks,
-Eric

 

[MajP]

The assumption is you are going to add that field.

 

[elsolo21]

Yep, I just got that...5 minutes too late to post it.

thanks for the help.