?
Solved

how to update with this query

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

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

585 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