'error 1175 when updating table by another table - WHERE clause exists

I'm trying to update a table using another table, like so, but I get error 1175 saying I don't have a WHERE clause when I do.

update t1 c, t2 dk
set c.date = dk.defaultdate, c.filter = 'word'  
where c.id = dk.id and c.name = dk.name and c.color = dk.color and c.country = dk.country
and c.id is not null and c.date is null and c.name is not null and c.color is not null and c.country is not null;

how can I make the needed change anyway? (im on MariaDB)

mysqlmariadbmysql-workbench


Solution 1:[1]

The first thing I would suggest here is to rewrite your update query using a proper explicit join:

UPDATE t1 c
INNER JOIN t2 dk
    ON c.id = dk.id AND c.name = dk.name AND c.color = dk.color AND
       c.country = dk.country
SET c.date = dk.defaultdate,
    c.filter = 'word'  
WHERE c.id IS NOT NULL AND c.date IS NULL AND c.name IS NOT NULL AND
      c.color IS NOT NULL AND c.country IS NOT NULL;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen

Related Questions