Using CAST in WHERE clause in MySQL Update Query-CodePudding

I have two tables, a local table in MS Access and a remote MySQL table linked via ODBC. They have identical order numbers except they are stored as a string in the remote table and a number in the local table. I can't seem to find how to update the local table. This was my initial code which gave me a 'Type Mismatch' error.

UPDATE
    localTable LT, remoteTable RT
SET
    LT.user = RT.user
WHERE
    LT.orderID = RT.orderID

I've tried using CAST but that doesn't seem to work either...

UPDATE
    localTable LT, remoteTable RT
SET
    LT.user = RT.user
WHERE
    LT.orderID = CAST(RT.orderID AS INTEGER)

I can't change the tables themselves to make them both string/number. Probably really simple but I can't seem to find the answer when using WHERE in an UPDATE query. Any help would be greatly received - thanks!

CodePudding user response：

In Access SQL, use Val:

UPDATE
    localTable LT, remoteTable RT
SET
    LT.user = RT.user
WHERE
    LT.orderID = Val(RT.orderID)

CodePudding user response：

I would use a JOIN instead of a where clause. Access allows joins in the UPDATE command. The string can be converted by using an Access conversion function like CLng, CDbl etc.

UPDATE
    localTable LT
    INNER JOIN remoteTable RT
        ON LT.orderID = CLng(RT.orderID)
SET
    LT.user = RT.user