Update linking back to the same table

[Jamfool]

Ok basically I need to copy the details of a main client to a new sub client related to that client.

so updating the address details of one Sub client (say with a code S1) with that of a Main client (M1)


UPDATE User_file
SET address1 =(SELECT Address1 FROM User_file WHERE (Code = @Main_Client)),
address2 =(SELECT Address2 FROM User_file WHERE (Code = @Main_Client)),
address3 =(SELECT Address3 FROM User_file WHERE (Code = @Main_Client)),
address4 =(SELECT Address4 FROM User_file WHERE (Code = @Main_Client)),
Postcode =(SELECT Postcode FROM User_file WHERE (Code = @Main_Client)),
DX =(SELECT DX FROM User_file WHERE (Code = @Main_Client)),
Tel_Day =(SELECT Tel_Day FROM User_file WHERE (Code = @Main_Client)),
Tel_Eve =(SELECT Tel_Eve FROM User_file WHERE (Code = @Main_Client)),
Tel_Mob =(SELECT Tel_Mob FROM User_file WHERE (Code = @Main_Client)),
Fax =(SELECT Fax FROM User_file WHERE (Code = @Main_Client))
WHERE code = @sub_client

 

[JeanNiBee]

You should look at how the UPDATE statement works again. You can do relations in T-SQL and avoid all those 'sub' selects.


Ex:

UPDATE User_file
SET 
    address1 = UF2.address1,
    address2 = UF2.address2,
    address3 = UF2.address3,
    address4 = UF2.address4,
    Postcode = UF2.Postcode,
    DX = UF2.DX,
    Tel_Day = UF2.Tel_Day,
    Tel_Eve = UF2.Tel_Eve,
    Tel_Mob = UF2.Tel_Mob,
    Fax = UF2.Fax
FROM User_file UF2
WHERE Code = UF2.Code
AND Code = @code

Hope this helps.

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects