Prev: Truncation at sixth decimal
Next: How to substitute for a non-existing column in a joined table
From: Steph on 21 Apr 2010 14:39
I want to update data in My SQL using linked servers in SQL Server 2000.
I want to know the syntax I have to write

I thought about something like that but it doesn't work...

update openquery(MysrvName, 'select * from tbl_data'), MyTable
set MyNote = MyTable.NewNote + MyNote
where MyID = MyTable.MyID


From: Erland Sommarskog on 21 Apr 2010 18:10
Steph (smarcoux(a)cbgi.qc.ca) writes:
> I want to update data in My SQL using linked servers in SQL Server 2000.
> I want to know the syntax I have to write
>
> I thought about something like that but it doesn't work...
>
> update openquery(MysrvName, 'select * from tbl_data'), MyTable
> set MyNote = MyTable.NewNote + MyNote
> where MyID = MyTable.MyID

This may work:

update MyTable
set MyNote = MyTable.NewNote + MyNote
from openquery(MysrvName, 'select * from tbl_data') AS MyTable
where MyID = MyTable.MyID



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Steph on 22 Apr 2010 10:39
What I 'm trying to do, is to update the data in MySQL (tbl_data) using
data from SQL Server (MyTable)
In your example, you put an alias to the openquery (MySQL) and name it like
my SQL Server table...

I modified your code a bit and got this error message

Derived table 'Mytbl' is not updatable because a column of the derived table
is derived or constant.


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D621B6DDACDYazorman(a)127.0.0.1...
> Steph (smarcoux(a)cbgi.qc.ca) writes:
>> I want to update data in My SQL using linked servers in SQL Server 2000.
>> I want to know the syntax I have to write
>>
>> I thought about something like that but it doesn't work...
>>
>> update openquery(MysrvName, 'select * from tbl_data'), MyTable
>> set MyNote = MyTable.NewNote + MyNote
>> where MyID = MyTable.MyID
>
> This may work:
>
> update MyTable
> set MyNote = MyTable.NewNote + MyNote
> from openquery(MysrvName, 'select * from tbl_data') AS MyTable
> where MyID = MyTable.MyID
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Erland Sommarskog on 22 Apr 2010 17:56
Steph (smarcoux(a)cbgi.qc.ca) writes:
> What I 'm trying to do, is to update the data in MySQL (tbl_data) using
> data from SQL Server (MyTable)

That wasn't clear. I had to guess what your private syntax was intended
to mean.

But in that case:

update openquery(MysrvName, 'select * from tbl_data')
set MyNote = (SELECT MyTable.NewNote
FROM MyTable
WHERE MyTable.MyID = MyID) + MyNote

or use four-part notation and the proprietary FROM syntax:

update MysrvName.db.schema.tbl_data
set MyNote = src.NewNote + trg.MyNote
FROM MysrvName.db.schema.tbl_data trg
JOIN MyTable stc ON src.MyID = trg.MyID

You will have to find out what to use for "db" and "schema".



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Truncation at sixth decimal
Next: How to substitute for a non-existing column in a joined table