• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4977
  • Last Modified:

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

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
immixGroup
Asked:
immixGroup
  • 3
1 Solution
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
immixGroupAuthor Commented:
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
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

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now