update MySQL from SQL Server

• martin.kerr 34088

  SSCommitted

  Points: 1752

  More actions

  May 13, 2013 at 4:14 am

  #275227

  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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 13, 2013 at 5:43 am

  #1614555

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!