VT12001: unsupported: unaliased multiple tables in update

[InsightfulFuture]

I recently turned on the foreign key beta and updated my tables to include foreign keys (ex: userId is a foreign key on the Tasks table).

Every time I try to run the simple update query below in the Planetscale console I get the error:
VT12001: unsupported: unaliased multiple tables in update

Update query:

UPDATE Tasks T
INNER JOIN Users U ON T.auth_id = U.auth_id
SET T.userId = U.userId;

[orware]

Hello @InsightfulFuture,

I believe you may have also wrote into support with the same question?

I'm sharing the same answer here in case it's useful for anyone else.

The query example you shared seems like it would fall under one of the currently unsupported cases for the new foreign key constraints functionality and is documented here:
https://planetscale.com/docs/concepts/foreign-key-constraints#unsupported-queries

And also here within Vitess itself:
https://github.com/vitessio/vitess/blob/main/go/vt/vtgate/planbuilder/testdata/unsupported_cases.json#L83-L85

It's likely that in the future the list of unsupported cases will become smaller, but for the moment you would likely have to split up your UPDATE query into separate queries so as to help avoid the current join it contains.

[InsightfulFuture]

Thank you. Can you help me understand what you mean by splitting up the query into multiple queries? For example, if there were 10,000 rows that we need to update, are you saying to first write a query to select and return the 10,000 userId values and then how would those be fed into the actual second update query without a join?