Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: update in MSSQL of MySQL Database via Linked MySQL-Database

    I'm using MSSQL Server 2008.
    I'm trying to update a MySQL database. I joined the MSSQL-Database with the MySQL-Database and now trying to complete the update, but having a strange problem.

    When I do the following Query, I get a limited amount of results:

    select *
    FROM [SQL-Database].[dbo].[Table] as c left join
    openquery(MYSQL, 'select * from MySQL-Database.Table')
    on c.KuNr=kunden_id collate German_PhoneBook_BIN
    where c.Auswahltyp='2' and c.[gelöscht]=0 and c.[Mietzeit bis]>GETDATE() and c.aktiveMail>'0'


    When I do the update, it updates ALL rows in database:

    update openquery(MYSQL, 'select * from MySQL-Database.Table')
    set paket_id=7
    FROM [SQL-Database].[dbo].[Table] as c left join
    openquery(MYSQL, 'select * from MySQL-Database.Table')
    on c.KuNr=kunden_id collate German_PhoneBook_BIN
    where c.Auswahltyp='2' and c.[gelöscht]=0 and c.[Mietzeit bis]>GETDATE() and c.aktiveMail>'0'


    The difference is just Select and Update, but it's kind of ignoring the "where" in the update.

  2. #2
    Join Date
    Aug 2012
    Posts
    3
    I'm a big step further with your query now.

    But still some difference

    Instead of wrongly updating the whole table (~14.000 rows) it now updates 514 rows. But the select query shows 537 rows :-/

    I now tried to update all rows (with query below). But it's just updating 1847 rows. Don't understand this. Could there be a misconfiguration in ODBC or MSDASQL?

    update d
    set d.paket_id=0
    FROM [SQL-Database].[dbo][Table] as c left join
    openquery(MYSQL, 'select * from MySQL-Database.Table') d
    on c.KuNr=d.kunden_id collate German_PhoneBook_BIN
    Last edited by ehs-verlag; 08-17-12 at 18:01.

  3. #3
    Join Date
    Aug 2012
    Posts
    3
    I'm just stupid ^^ I was so deep in getting the query to work and show the same results like the select, that I forgot, that the SQL-Table has some double occurring values. Just checked the double values and voilá matches the sum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •