Solved

how to update with this query

Posted on 2013-01-16
4
233 Views
Last Modified: 2013-01-18
Hi!

Have this update query:

UPDATE SALETMP
SET SALE.version_id = FAKT.version_id
FROM FACT_Sale_tmp as SALETMP,FACT_Forcast_Hit_Ratio FAKT 
where FAKT.DATAAREAID=SALETMP.DATAAREAID
and FAKT.FORECASTYEARWEEK = SALETMP.FORECASTYEARWEEK

Open in new window


Get this error message:
Error Code: 4104, SQL State: S1000]  The multi-part identifier "SALE.version_id" could not be bound.

Table SALETMP.FORECASTYEARWEEK contains of more then 1 record with the same
value.

Example:

SALE

201201
201201
201202
201202
201202
201202
201203
201204

How can i fix this ?
0
Comment
Question by:team2005
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38782428
please read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

I presume it's just a typo, it should be SALETMP.version_id  and not SALE.version_id
UPDATE SALETMP
SET SALETMP.version_id = FAKT.version_id
FROM FACT_Sale_tmp as SALETMP
,FACT_Forcast_Hit_Ratio FAKT 
where FAKT.DATAAREAID=SALETMP.DATAAREAID
and FAKT.FORECASTYEARWEEK = SALETMP.FORECASTYEARWEEK

Open in new window

0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 38782436
what is SALE. ? it is not an alias.

Your query should read (no alias in the set clause - I have also used a INNER JOIN):

UPDATE SALETMP
SET version_id = FAKT.version_id
FROM FACT_Sale_tmp as SALETMP
INNER JOIN FACT_Forcast_Hit_Ratio FAKT
ON FAKT.DATAAREAID=SALETMP.DATAAREAID
and FAKT.FORECASTYEARWEEK = SALETMP.FORECASTYEARWEEK
0
 
LVL 10

Expert Comment

by:Monica P
ID: 38787030
The multi-part identifier error will be shown when u r trying to use the variable or table name that is not part of select statement or  in innerjoin,even though those table appear in Database

Posted Code
**********

UPDATE SALETMP
SET SALE.version_id = FAKT.version_id
FROM FACT_Sale_tmp as SALETMP,FACT_Forcast_Hit_Ratio FAKT
where FAKT.DATAAREAID=SALETMP.DATAAREAID
and FAKT.FORECASTYEARWEEK = SALETMP.FORECASTYEARWEEK

Updated Code ..Try with this
*************
UPDATE SALETMP
SET SALETMP.version_id = FAKT.version_id
FROM FACT_Sale_tmp as SALETMP,FACT_Forcast_Hit_Ratio FAKT
where FAKT.DATAAREAID=SALETMP.DATAAREAID
and FAKT.FORECASTYEARWEEK = SALETMP.FORECASTYEARWEEK
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38792723
thanks
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now