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.
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.