Solved

SQL 2008 Linked Table UPDATE with INNER JOIN syntax help.

Posted on 2010-09-23
1
537 Views
Last Modified: 2012-05-10
Hello Group;
I am trying to update a Linked Server table with the following query but have varying errors.
Please let me know what the heck I am doing wrong here.  Because it is a linked server, the tables initially need to be called out in Four Dot notation.

UPDATE [VANTAGE TEST].[MFGSYS].[PUB].[PartPlant]
SET DaysOfSupply = 10
FROM(SELECT DaysOfSupply FROM [VANTAGE TEST].[MFGSYS].[PUB].[PartPlant]
INNER JOIN [VANTAGE TEST].[MFGSYS].[PUB].[Part]
ON PartPlant.PartNum = Part.PartNum WHERE Part.TypeCode ="P")

Goal: Update the PartPlant.DaysOfSupply with a value of 10.  I get errors like Cannot update VarChar field with Integer, but when I try to put the value of 10 in quotes I get other errors.

Thanks for any help!
GLH
0
Comment
Question by:PCI-INTL
[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
1 Comment
 

Accepted Solution

by:
PCI-INTL earned 0 total points
ID: 33749714
Got it!  Just had to change the "ON" portion reverse the table names and use single quotes.

UPDATE [VANTAGE TEST].[MFGSYS].[PUB].[PartPlant]
SET DaysOfSupply = 14
SELECT DaysOfSupply FROM [VANTAGE TEST].[MFGSYS].[PUB].[PartPlant]
INNER JOIN [VANTAGE TEST].[MFGSYS].[PUB].[Part]
ON Part.PartNum = PartPlant.PartNum WHERE Part.TypeCode = 'P'
GO
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql how to count case when 4 29
Present Absent from working date rage 11 49
Check ALL SP in database make sure there are no errors 17 61
Stored Proc - Rewrite 42 73
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

749 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