Reputation: 103
I am running into an issue where My first query ( SELECT ) works fine but When I try to execute my second query ( UPDATE ) I get an error. Can someone please help me to know what am I doing wrong here?
QUERY 1:
WITH res AS (
SELECT str.request_id as requestID, str.type as incorrectType, TxType.utType as correctType
FROM x str
JOIN (
SELECT tr.request_id, ut.type AS utType
FROM x tr
JOIN y tc ON tr.request_id = tc.request_id
JOIN z ut ON tc.transaction_id = ut.id
) AS TxType ON str.request_id = TxType.request_id
WHERE str.type != TxType.utType
AND str.application = 'sample' LIMIT 1
)
SELECT * FROM x tr_req
JOIN res AS re ON re.requestID = tr_req.request_id
WHERE tr_req.type != re.correctType;
RESULT : SUCCESS
QUERY : 2
WITH res AS (
SELECT str.request_id as requestID, str.type as incorrectType, TxType.utType as correctType
FROM x str
JOIN (
SELECT tr.request_id, ut.type AS utType
FROM x tr
JOIN y tc ON tr.request_id = tc.request_id
JOIN z ut ON tc.transaction_id = ut.id
) AS TxType ON str.request_id = TxType.request_id
WHERE str.type != TxType.utType
AND str.application = 'sample' LIMIT 1
)
UPDATE x tr_req
JOIN res AS re ON re.requestID = tr_req.request_id
SET tr_req.type = re.correctType
WHERE tr_req.type != re.correctType;
RESULT : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE x tr_req
Upvotes: 0
Views: 207
Reputation: 16804
You're using MariaDB. Here is the documentation of WITH.
As you can see in the syntax, it works only with SELECT:
WITH [RECURSIVE] table_reference [(columns_list)] AS (
SELECT ...
)
[CYCLE cycle_column_list RESTRICT]
SELECT ...
There is no UPDATE option.
Upvotes: 1