jwcolby
jwcolby at colbyconsulting.com
Fri Sep 17 14:39:18 CDT 2010
No problem. Very cool. I created the query I needed in about 3 minutes and tested. Very nice. John W. Colby www.ColbyConsulting.com On 9/17/2010 3:32 PM, David McAfee wrote: > oops, sorry about that. I didn't test it, just typed it into gmail, so > I didn't catch that. > > > On Fri, Sep 17, 2010 at 12:27 PM, jwcolby<jwcolby at colbyconsulting.com> wrote: >> OK, it had to be UPDATE A >> SET A.FName = B.Fname >> FROM PSM11229.dbo.AZData as A >> INNER JOIN PSM11229_Temp.dbo.AZData AS B >> ON A.PK = B.PK >> >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 9/17/2010 3:13 PM, jwcolby wrote: >>> David, >>> >>> UPDATE PSM11229.dbo.AZData >>> SET A.FName = B.FName >>> FROM PSM11229.dbo.AZData as A >>> INNER JOIN PSM11229_Temp.dbo.AZData AS B >>> ON A.PK = B.PK >>> >>> Compiles but fails to run with an error >>> >>> The multi-part identifier "A.FName" could not be bound. >>> >>> >>> John W. Colby >>> www.ColbyConsulting.com >>> >>> On 9/17/2010 2:51 PM, David McAfee wrote: >>>> Have you tried it this way? >>>> >>>> UPDATE TABLEA >>>> SET >>>> A.FIELD1 = B.FIELD1, >>>> A.FIELD2 = B.FIELD2, >>>> A.FIELD3 = B.FIELD3, >>>> A.FIELD4 = B.FIELD4, >>>> A.FIELD5 = B.FIELD5, >>>> A.FIELD6 = B.FIELD6, >>>> A.FIELD7 = B.FIELD7, >>>> A.FIELD8 = B.FIELD8, >>>> A.FIELD9 = B.FIELD9, >>>> A.FIELD10 = B.FIELD10, >>>> A.FIELD11 = B.FIELD11, >>>> A.FIELD12 = B.FIELD12, >>>> A.FIELD13 = B.FIELD13, >>>> A.FIELD14 = B.FIELD14, >>>> A.FIELD15 = B.FIELD15, >>>> A.FIELD16 = B.FIELD16, >>>> FROM TABLEA AS A >>>> INNER JOIN TABLEB AS B >>>> ON A.ID = B.ID >>>> >>>> >>>> On Fri, Sep 17, 2010 at 11:28 AM, jwcolby<jwcolby at colbyconsulting.com> wrote: >>>>> UPDATE TABLEA SET >>>>> FIELD1 = (SELECT TABLEB.FIELD1 FROM TABLEB WHERE TABLEB.ID = TABLEA.ID), >>>>> FIELD2= (SELECT TABLEB.FIELD2 FROM TABLEB WHERE TABLEB.ID = TABLEA.ID) >>>>> >>>>> It seems darned ugly but I can see the logic. However I have about 16 fields to update for (in some >>>>> cases) a subset of 2 million records in a 65 million record table. >>>>> >>>>> It seems like this is going to be very inefficient. >>>>> >>>>> -- >>>>> John W. Colby >>>>> www.ColbyConsulting.com >>>>> -- >>>>> AccessD mailing list >>>>> AccessD at databaseadvisors.com >>>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>>> Website: http://www.databaseadvisors.com >>>>> >>>> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >