Search code examples
mysqlsqldatabasejoininner-join

Updating database table based on condition set in another table?

How would I set a condition between 2 tables?

1 table has IDs whose values that need to be changed based on values set in another table.

The query I have so far...no idea if it works.

UPDATE table1 set value='2' INNER JOIN table2 WHERE CONVERT(value USING utf8) LIKE '%text%') so when value is 2 in the first table, that same item in table 2 will be assigned a specific category. The query should check that the ID in table 2 is the same ID that it found in table 1 that contained the value.

Solution

  • Your code looks like MySQL. If so, the correct syntax is more like this:

    UPDATE table1 t1 INNER JOIN
       table2 t2
       ON t1.id = t2.id
    set t1.value = '2' 
WHERE CONVERT(t1.value USING utf8) LIKE '%text%');

    Your question is a bit vague on the actual JOIN conditions, but that is the structure of the query.