?
Solved

T-SQL Update Inner Join Syntax

Posted on 2006-06-02
5
Medium Priority
?
165,825 Views
Last Modified: 2011-10-12
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?
0
Comment
Question by:Toast_Boy
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16816837
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'
0
 
LVL 1

Author Comment

by:Toast_Boy
ID: 16831163
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?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 16831241
I now see you want to update 2 tables at once.
while this works in Access, it does NOT work in other databases, you have to run 2 update statements:

UPDATE CUSTOMER
SET 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'


UPDATE PROSPECTIVE_CUSTOMER
SET PLACE_STATUS = 'A'
FROM CUSTOMER
INNER JOIN PROSPECTIVE_CUSTOMER
  ON CUSTOMER.CUST_ID = PROSPECTIVE_CUSTOMER.CUST_ID
WHERE CUSTOMER.IN_USE='Y'
AND CUSTOMER.RECORD_TYPE='2'
0
 
LVL 1

Author Comment

by:Toast_Boy
ID: 16831377
Thanks for clarifying this
0
 
LVL 10

Expert Comment

by:itcouple
ID: 36956356
link to SQL Update Join
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question