asked on

T-SQL Update Inner Join Syntax

Quick syntax question

I have a TSQL statement that needs to update a field on both tables.

The statement is :

UPDATE
CUSTOMER
INNER JOIN PROSPECTIVE_CUSTOMER ON CUSTOMER.CUST_ID = PROSPECTIVE_CUSTOMER.CUST_ID
SET
PROSPECTIVE_CUSTOMER.PLACE_STATUS = 'A',
CUSTOMER.START_DATE = '2006-9-2 00:00:00.000'
WHERE CUSTOMER.IN_USE='Y'
AND CUSTOMER.RECORD_TYPE='2'

This will work in ACCESS, but I want to run it in query analyser, I know the syntax is similar to

UPDATE
TABLE
SET FIELD = VALUE
INNER JOIN TABLE ON TABLE1 TABLE.ID = TABLE1.ID

But I can't find any referenec to what I am trying to achieve with the first script

Any ideas?

Guy Hengel [angelIII / a3]

UPDATE CUSTOMER
SET PROSPECTIVE_CUSTOMER.PLACE_STATUS = 'A'
, CUSTOMER.START_DATE = '2006-9-2 00:00:00.000'
FROM CUSTOMER
INNER JOIN PROSPECTIVE_CUSTOMER
ON CUSTOMER.CUST_ID = PROSPECTIVE_CUSTOMER.CUST_ID
WHERE CUSTOMER.IN_USE='Y'
AND CUSTOMER.RECORD_TYPE='2'

Toast_Boy

ASKER

Unfortunatly that statement did'nt work.

It comes up with :

Server: Msg 1032, Level 15, State 1, Line 3
Cannot use the column prefix 'PROSPECTIVE_CUSTOMER'. This must match the object in the UPDATE clause 'CUSTOMER'.

Any ideas?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Toast_Boy

ASKER

Thanks for clarifying this

itcouple

link to SQL Update Join