Solved

how to update with this query

Posted on 2013-01-16
4
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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 …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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