Solved

how to update with this query

Posted on 2013-01-16
4
228 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 69

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

21 Experts available now in Live!

Get 1:1 Help Now