Solved

Multi-part identifier could not be bound- SQL update queries based off of matching variables in two tables - URGENT

Posted on 2007-03-31
4
4,948 Views
Last Modified: 2012-06-22
I'm trying to update a field in table 1 (pricing.mod_type)to = a field in table 2 (archive.pendingmod),  but I only want to select the most recent of [archive.pendingmod] based off of a incrementing key field. I am matching the two tables based on a master number, which appears multiple times in the archive (one record per different pendingmod, but i just want the most recent, hence selecting the max of the key). I am also matching this where archive.contractlistprice=pricing.contractlistprice and only updating those where pricing.contract='xxxxxxxxx' (obscured for company privacy)

Here is my code:

update pricing
set [pricing].[mod_type]=[archive].[pendingmod]
      select max([archive].[key])
      from [archive]
      where [pricing].[immixmasterno]=[archive].[immixmasterno]
            and [pricing].[contractlistprice]=[archive].[contractlistprice]
            and [pricing].[contract]='XXXXXXj'

And here are my errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "archive.pendingmod" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.immixmasterno" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.contractlistprice" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "pricing.contract" could not be bound.


0
Comment
Question by:immixGroup
[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
  • 3
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18828889
Something like this:

update      p
set            mod_type = a.pendingmod
from      pricing p
            Inner Join (
                        Select      Top 1
                                    pendingmod,
                                    immixmasterno,
                                    contractlistprice
                        From      archive
                        Order By
                                    [key] DESC) a On p.immixmasterno = a.immixmasterno
                              and p.contractlistprice = a.contractlistprice
Where      p.[contract]='XXXXXXj'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18828898
On second thoughts, this may be what you need:

update      p
set            mod_type =       (Select      Top 1
                                          pendingmod,
                              From      archive
                              Where      immixmasterno = p.immixmasterno
                                          and contractlistprice = p.contractlistprice
                              Order By
                                          [key] DESC
from      pricing p
Where      [contract]='XXXXXXj'
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18828902
Oops, let's try that again:

update      p
set            mod_type =       (Select      Top 1
                                          pendingmod
                              From      archive
                              Where      immixmasterno = p.immixmasterno
                                          and contractlistprice = p.contractlistprice
                              Order By
                                          [key] DESC)
from      pricing p
Where      [contract]='XXXXXXj'
0
 

Author Comment

by:immixGroup
ID: 18832430
AC, thanks for the response, sorry I didn't award points sooner. I might have a similar help request with the same reward in a few minutes if you're interested. Thanks again for the help!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

729 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