Converting WITH statement from Postgres to mySQL

Question

I'm working on porting a database system from PostgreSQL to mySQL but I can't get my head round this simply query?

The PostgreSQL query looks like the following:

query('WITH t as (UPDATE sessions SET expired = now() WHERE id = $1 AND ott = TRUE RETURNING *)' +
        'SELECT * FROM users WHERE id = (SELECT user_id FROM t)',
        [token], function(err, result) {
            if (err) return callback(err);
            if (result.rows === 0) return callback('NOT_VALID_TOKEN');
            assert(result.rows.length === 1);
            callback(null, result.rows[0]);
        }
    );

However when trying to write this into mySQL as such:

WITH t as (UPDATE sessions SET expired = now() WHERE id = 1 AND ott = TRUE RETURNING *) SELECT * FROM users WHERE id = (SELECT user_id FROM t)

It's throwing this:

1 errors were found during analysis.

Unrecognized statement type. (near "WITH" at position 0)

score 3 · Accepted Answer · answered Jan 15 '20 at 19:26

While versions of MySQL before 8.0 do not support CTEs (WITH statements) it really doesn't matter since the big miss here is that MySQL doesn't support the RETURNING clause of the UPDATE statement like postgres does.

Instead you will need to execute two statements.

Update:

UPDATE sessions SET expired = now() WHERE id = 1 AND ott = TRUE;

Select using the same predicates:

SELECT * FROM users WHERE id = 1 AND ott = TRUE;

It's not as convenient as Postgres, but it does the job.

score 0 · Answer 2 · answered Jan 15 '20 at 19:23

0

MySQL 8.0 does support common table expressions: Earlier versions unfortunately do not.

You could try using derived tables in the earlier versions.

Converting WITH statement from Postgres to mySQL

2 Answers2