The sub-query must yield no more than one row when executed.If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns.
Example: Update-Select-Group By I have a table of totals 'v8totals' with Primary Key=(tid,tyy,tmm) and a table of records 'bbprepay'.So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement: UPDATE table SET c=c 1 WHERE a=1 AND b=2;(and not "a=1 OR b=2") Regarding the trick for making LAST_INSERT_ID() well defined for updates: INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; This does not work if nothing changes, presumably because My SQL doesn't run the dummy update at all then. if there is an existing record with 3 in column c then LAST_INSERT_ID() still won't return the AUTO_INCREMENT id afterwards.I'm not sure whether this should be regarded as a bug or not, but it does make the construct less useful.The sub-query can refer to old values of the current row of the table being updated.you should ensure that the join produces at most one output row for each row to be modified.Subclasses should override the default behavior, which is a straight identity comparison.**kw are arguments consumed by subclass compare() methods and may be used to modify the criteria for comparison. Common table expressions are a SQL standard whereby SELECT statements can draw upon secondary statements specified along with the primary statement, using a clause called “WITH”.In other words, a target row shouldn't join to more than one row from the other table(s).If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.For more information, see Section 18.104.22.168, “CREATE TABLE Statement Retention”.There are several aspects to the There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section C.10.4, “Limits on Table Column Count and Row Size”.