May 13, 2013 at 4:14 am
Hi All,
I have managed to set up a linked server to a MySQL database and i want to update the MySQL database using a table within SQL Server. I tried the below code:
UPDATE OPENQUERY(DQ_TESTDATA,'select webmaster_id, dupe_master_id, dupe_flag from webmaster')
SET dupe_master_id = r.MID,
dupe_flag = 'M'
FROM (Select * from dbo.DQResults) as r INNER JOIN
OPENQUERY(DQ_TESTDATA,'select webmaster_id from webmaster') AS w
ON r.MID = w.webmaster_id
but it updated every record in the database (just over 2 million).
it should only be updating around 662510.
Can anyone see why this is happening and what the SQL update query should be?
Thanks
May 13, 2013 at 5:43 am
martin.kerr 34088 (5/13/2013)
Hi All,I have managed to set up a linked server to a MySQL database and i want to update the MySQL database using a table within SQL Server. I tried the below code:
UPDATE OPENQUERY(DQ_TESTDATA,'select webmaster_id, dupe_master_id, dupe_flag from webmaster')
SET dupe_master_id = r.MID,
dupe_flag = 'M'
FROM (Select * from dbo.DQResults) as r INNER JOIN
OPENQUERY(DQ_TESTDATA,'select webmaster_id from webmaster') AS w
ON r.MID = w.webmaster_id
but it updated every record in the database (just over 2 million).
it should only be updating around 662510.
Can anyone see why this is happening and what the SQL update query should be?
Thanks
there's no WHERE statement to discriminate/filter which rows you want to affect.
don't you need something like
WHERE dupe_master_id = r.MID
AND dupe_flag <> 'M'
so it only affects the rows that are not yet "M" for the dupe_flag?
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy