Try one of these two choices, one using a JOIN, one using a Sub-SELECT:
UPDATE n
SET n.Field = o.Field
FROM NewDatabase.dbo.Table n
JOIN OldDatabase.dbo.Table o
ON n.PK = o.PK
(OR)
UPDATE NewDatabase.dbo.Table n
SET n.Field = (SELECT o.FIELD
FROM OldDatabase.dbo.Table o
WHERE o.Field = n.Field
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"John Hackert" <hackertjohnb@.yahoo.com> wrote in message news:1162174049.649530.85940@.f16g2000cwb.googlegro ups.com...
>I haven't been able to solve this likely syntax problem despite
> referring to these groups, the Books On Line, and reference texts:
> Without the use of Data Transformation Services in SQL Server Express,
> what techniques work to import/export data between databases under the
> same server instance?
> The specific example I was working on involved an attempt to run an
> update query to place data from a specific field in an older copy of an
> otherwise identical database into a more recent copy. Ultimately I
> just plugged the small data series in manually using side-by-side views
> because I could not overcome the syntax errors generated in the effort
> to refer to the "external" database.
> This is the setup in question:
> - An instance in the format of "MyComputerName\SQLExpress," and both an
> - "OldDatabase" and
> - "NewDatabase" attached and viewable from the Management Studio
> Express, with identical fields
> I tried a few different query techniques as suggested in historical
> posts, but to no avail. Here is such an example that I tried:
> In MSE I right clicked on the target table in question and chose:
> "Script table as," then
> "UPDATE to," then
> "New Query Editor Window"
> I removed all the fields except that in question and added this Where
> clause:
> UPDATE [NewDatabase].[dbo].[Table]
> SET [Field] = [OldDatabase].[dbo].[Table].[Field]
> WHERE [NewDatabase].[dbo].[Table].[PK] =
> [OldDatabase].[dbo].[Table].[PK]
> in which the primary key (an auto-increment integer) is identical
> between the new and old tables.
> This produces the error:
> Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "OldDatabase.dbo.Table.PK" could not be
> bound.
> If I understood from prior posts, using joins is less desirable in
> update queries, but even so likewise produced errors when I tried that
> approach.
> I would be grateful if someone could explain the proper syntax to refer
> to another database within the same server instance or otherwise.
> Many thanks!
>
Thank you for replying -
Even with these other techniques, I receive the same "multi-part
identifier...could not be bound" error. So, perhaps the root of the
problem is not the syntax of the query but something else. Any other
ideas?
John H
Arnie Rowland wrote:
> Try one of these two choices, one using a JOIN, one using a Sub-SELECT:
> UPDATE n
> SET n.Field = o.Field
> FROM NewDatabase.dbo.Table n
> JOIN OldDatabase.dbo.Table o
> ON n.PK = o.PK
> (OR)
> UPDATE NewDatabase.dbo.Table n
> SET n.Field = (SELECT o.FIELD
> FROM OldDatabase.dbo.Table o
> WHERE o.Field = n.Field
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "John Hackert" <hackertjohnb@.yahoo.com> wrote in message news:1162174049.649530.85940@.f16g2000cwb.googlegro ups.com...
> --=_NextPart_000_01FC_01C6FB8D.01E3E1B0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5469
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>Try one of these two choices, one using a JOIN, one
> using a Sub-SELECT:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face="Courier New" size=2>UPDATE n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2> SET n.Field =
> o.Field</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>FROM NewDatabase.dbo.Table n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2> JOIN OldDatabase.dbo.Table
> o</FONT></DIV>
> <DIV><FONT face="Courier New" size=2> &am p;nbsp; ON n.PK =
> o.PK<BR></FONT></DIV>
> <DIV><FONT face="Courier New" size=2>(OR)</FONT></DIV>
> <DIV><FONT face="Courier New" size=2></FONT> </DIV>
> <DIV><FONT face="Courier New" size=2>UPDATE NewDatabase.dbo.Table n</FONT></DIV>
> <DIV><FONT face="Courier New" size=2> SET n.Field = (SELECT
> o.FIELD</FONT></DIV>
> <DIV><FONT face="Courier New"
> size=2> &am p;nbsp; &am p;nbsp; &am p;nbsp;
> FROM OldDatabase.dbo.Table o</FONT></DIV>
> <DIV><FONT face="Courier New"
> size=2> &am p;nbsp; &am p;nbsp; &am p;nbsp;
> WHERE o.Field = n.Field</FONT></DIV>
> <DIV><FONT size=2><FONT
> face="Courier New"> & nbsp; & nbsp; & nbsp;
> )</FONT></DIV></FONT>
> <DIV><FONT face=Arial size=2></FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>"John Hackert" <</FONT><A
> href="http://links.10026.com/?link=mailto:hackertjohnb@.yahoo.com"><FONT face=Arial
> size=2>hackertjohnb@.yahoo.com</FONT></A><FONT face=Arial size=2>> wrote in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162174049.649530.85940@.f16g2000cwb.goo glegroups.com"><FONT
> face=Arial
> size=2>news:1162174049.649530.85940@.f16g2000cwb.go oglegroups.com</FONT></A><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I haven't been
> able to solve this likely syntax problem despite<BR>> referring to these
> groups, the Books On Line, and reference texts:<BR>> <BR>> Without the use
> of Data Transformation Services in SQL Server Express,<BR>> what techniques
> work to import/export data between databases under the<BR>> same server
> instance?<BR>> <BR>> The specific example I was working on involved an
> attempt to run an<BR>> update query to place data from a specific field in an
> older copy of an<BR>> otherwise identical database into a more recent
> copy. Ultimately I<BR>> just plugged the small data series in manually
> using side-by-side views<BR>> because I could not overcome the syntax errors
> generated in the effort<BR>> to refer to the "external" database.<BR>>
> <BR>> This is the setup in question:<BR>> - An instance in the format of
> "MyComputerName\SQLExpress," and both an<BR>> - "OldDatabase" and<BR>> -
> "NewDatabase" attached and viewable from the Management Studio<BR>> Express,
> with identical fields<BR>> <BR>> I tried a few different query techniques
> as suggested in historical<BR>> posts, but to no avail. Here is such an
> example that I tried:<BR>> <BR>> In MSE I right clicked on the target
> table in question and chose:<BR>> "Script table as," then<BR>> "UPDATE
> to," then<BR>> "New Query Editor Window"<BR>> <BR>> I removed all the
> fields except that in question and added this Where<BR>> clause:<BR>>
> UPDATE [NewDatabase].[dbo].[Table]<BR>> SET [Field] =
> [OldDatabase].[dbo].[Table].[Field]<BR>> WHERE
> [NewDatabase].[dbo].[Table].[PK] =<BR>>
> [OldDatabase].[dbo].[Table].[PK]<BR>> <BR>> in which the primary key (an
> auto-increment integer) is identical<BR>> between the new and old
> tables.<BR>> <BR>> This produces the error:<BR>> Msg 4104, Level 16,
> State 1, Line 1<BR>> The multi-part identifier "OldDatabase.dbo.Table.PK"
> could not be<BR>> bound.<BR>> <BR>> If I understood from prior posts,
> using joins is less desirable in<BR>> update queries, but even so likewise
> produced errors when I tried that<BR>> approach.<BR>> <BR>> I would be
> grateful if someone could explain the proper syntax to refer<BR>> to another
> database within the same server instance or otherwise.<BR>> <BR>> Many
> thanks!<BR>></FONT></BODY></HTML>
> --=_NextPart_000_01FC_01C6FB8D.01E3E1B0--
|||Thank you for replying
I thought this post was considered dead, so I elected to repost the
question on microsoft.public.sqlserver.server. The syntax proposed by
"amish" on that group proved to work:
UPDATE [TestNew].[dbo].[tblProcedure]
SET [CPT] = TestOld.[CPT]
FROM [TestNew].[dbo].[tblProcedure] TestNew inner join
[TestOld].[dbo].[tblProcedure] TestOld
on
TestNew.[procedureID]=TestOld.[procedureID]
Hugo Kornelis wrote:
> On 30 Oct 2006 11:19:51 -0800, John Hackert wrote:
>
> Hi John,
> Please post the COMPLETE query, and the COMPLETE error message. Use copy
> and paste to prevent errors. That information can help us pinpoint the
> problem.
> --
> Hugo Kornelis, SQL Server MVP
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment