Re: Change from Postgre to MySQL query error

[Devart Team]

Firstly, try to write a query that results rows you want to update. This query can be like this -

SELECT * FROM datavalue d1
  LEFT JOIN datavalue d2
  ON d2.dataelementid = 1259 AND
    d2.categoryoptioncomboid = 1 AND
    d1.periodid = d2.periodid AND
    d1.sourceid = d2.sourceid
WHERE d1.dataelementid = 1261 AND
  d1.categoryoptioncomboid = 1 AND
  d2.dataelementid IS NULL;

Check the result of this query. Then, apply it to the UPDATE query, for example -

UPDATE datavalue d1
  LEFT JOIN datavalue d2
  ON d2.dataelementid = 1259 AND
    d2.categoryoptioncomboid = 1 AND
    d1.periodid = d2.periodid AND
    d1.sourceid = d2.sourceid
SET
  d1.dataelementid = 1259, d1.categoryoptioncomboid = 1
WHERE d1.dataelementid = 1261 AND
  d1.categoryoptioncomboid = 1 AND
  d2.dataelementid IS NULL;

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/