Updating SQL DB fields with multiple target records

Hi,

I am trying to multiple update records in Table B from a single record in Table A.  To identify the records that need to be updated I used this:

Select

Table1.cfirstnameas'Table 1 First',Table1.clastnameas'Table 1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,

Table2.cfirstnameas'Table 2 First',Table2.cLastNameas'Table 2 Last',Table2.ilocationidas'Table 2 Location',Table1.iLocationIDas'Table 1 Location'

fromTable1

JoinTable3 CRonTable1.istudentid=CR.istudentid

JoinTable2 onCR.icontactid=Table2.iContactID

whereCR.lLivesWithStudent=1 andTable1.ifamilyid>0 andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1

I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2.  I needed Table 3 to make the connection from Table 1 to 2

Thank you

April 30th, 2015 3:09pm

please carefully update.. you begin tran as start the transaction and verify the data before you committ.

the code will update TABLE2 - ILOCATIONID with TABLE1 - ILOCATIONID . make sure that is what you wanted.

Begin Tran

--updates locationID in table B with LocationID in table A
Update B Set B.ilocationid=A.iLocationID
from Table1 A
Join Table3 CR on A.istudentid=CR.istudentid
Join Table2 B on CR.icontactid=B.iContactID
where CR.lLivesWithStudent=1 and A.ifamilyid>0 and A.ilocationid<>B.iLocationID and A.lCurrent=1


--verify the data
select  * from Table2
--if right, commit
--Commit
--if wrong, rollback
--Rollback()

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 5:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics