Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to update with this query

Posted on 2013-01-16
4
Medium Priority
?
249 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 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

916 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