MySQL Bugs: #6119: Update multiple tables fails with "ERROR 1032: Can't find record in 'table'"

Bug #6119	Update multiple tables fails with "ERROR 1032: Can't find record in 'table'"
Submitted:	15 Oct 2004 18:23	Modified:	19 Oct 2004 18:43
Reporter:	Jianing Hu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.22	OS:	FreeBSD (free BSD)
Assigned to:		CPU Architecture:	Any

Description:
Updated multiple tables using outer join will fail if one of the tables being updated is empty. The error message is:
ERROR 1032: Can't find record in <table>

For example, the following SQL
update a left join b on a.id=b.id set a.status='standby', b.status='standby' where a.id=123;
will fail if table b is empty, even when there is a record in table a with id=123. Since outer join is being used, it should be valid if all tables being updated are not empty, and the command should succeed in updating the record in table a.

How to repeat:
See the example in description.

Suggested fix:
See description.

Can you please elaborate more on this?

> Since outer join is being used, it should be valid if all tables being
> updated are not empty, and the command should succeed in updating the record in
> table a.

In your example only one of the tables is non-empty not all.

I have to mention that multi table update is non-standard feature so it's behaviour is not defined by the SQL standard.

What I meant is that not all tables are empty. I see this as updating a view that's constructed by an outer join, and since the view is not empty, the update should be allowed (and the empty table ignored).

But it's a good point that the standard doesn't define the behavior of updating a view constructed from a join. One can argue that since table b is empty, it cann't be updated and therefore the view cannot be updated. But I'd say it's more natual to update table a and ignore b in this case.

I don't think it's "more natural". In the spirit of SQL each query is atomic - all or nothing. It's not acceptable to update only one table in multi-table update statement.