Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update MySQL from SQL Server Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 4:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:46 AM
Points: 56, Visits: 94
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
Post #1452034
Posted Monday, May 13, 2013 5:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse