Search code examples
mysqlsqlphpmyadminsql-updateinner-join

Update table from converted value in another table

I'm trying to compare between an int value and a converted (varchar to INT) value in one table to find a value in another table then update it through phpmyadmin.

comment table

id: 1   title:Lorem   postID: 15

meta table

ID:99   NewID: 123 Type: "older_id"   Value:"15"

SQL

update t1
set t1.postID = t2.NewID
from comment t1
inner join meta t2
on t1.postID = CAST(t2.value AS INT)
where t2.Type = "older_id";

I'm not sure what i'm doing wrong but i keep getting an error.

Solution

  • If you are using MySQL (which is typical with PHP), then the right syntax is:

    update comment c join
       meta m
       on c.postId = (p2.value + 0)  -- this converts the value, although even this is not necessary
    set c.postID = m.NewID
    where m.Type = 'older_id';

    There is no from clause in MySQL.