Click here to Skip to main content
15,312,540 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
i am updating one table, in that query in where clause want to match profile_id of that table and other so how to do it???

query

SQL
UPDATE QAProfile.HDR_PROFILE
INNER JOIN QASupport.PROFILE_COUNTRY_SUPPORT  
ON (QASupport.PROFILE_COUNTRY_SUPPORT.PROFILE_ID=QAProfile.HDR_PROFILE.PROFILE_ID)
SET CITY_ID=1,LOCATION_ID=2, UPD_USER_ID=1, UPD_USER_DATE=getdate()
WHERE COUNTRY_ID=1


getting error Incorrect syntax near the keyword 'INNER'
Posted
Updated 25-Mar-13 21:53pm
v2

Try:
SQL
UPDATE 
    QAProfile.HDR_PROFILE
SET 
    CITY_ID=1, LOCATION_ID=2, UPD_USER_ID=1, UPD_USER_DATE=getdate()
FROM 
    QAProfile.HDR_PROFILE A
INNER JOIN 
    QASupport.PROFILE_COUNTRY_SUPPORT B
ON 
   B.PROFILE_ID=A.PROFILE_ID
WHERE COUNTRY_ID=1

Refer: Examples: join in SQL update statement[^]
   
v2
Hi,

Check the Following Logic....
SQL
-- Multiple Update 
UPDATE PH SET PH.CITY_ID=1,PH.LOCATION_ID=2, PH.UPD_USER_ID=1, PH.UPD_USER_DATE=getdate()
FROM QAProfile.HDR_PROFILE PH
INNER JOIN QASupport.PROFILE_COUNTRY_SUPPORT PC ON PC.PROFILE_ID=PH.PROFILE_ID
WHERE PH.COUNTRY_ID=1
--Note : Check the tables Names Properly.... Because in ur Question I am not able to Find the name. 
   
v2
Use merge instead of update as below

MERGE INTO Sales_Import
USING RetrieveAccountNumber
ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
WHEN MATCHED THEN
UPDATE
SET AccountNumber = RetrieveAccountNumber.AccountNumber;


http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900