Click here to Skip to main content
15,669,524 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[^]
 
Share this answer
 
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. 
 
Share this answer
 
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[^]
 
Share this answer
 
 
Share this answer
 

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