model()->updateAll() Error when Using Join #957

Closed
Flajavin opened this Issue Jul 12, 2012 · 6 comments

Projects

None yet

3 participants

@Flajavin

I have an advanced search on my website that generates a CDbCriteria with join (inner join) with multiple tables. After the search I added the option to select all the results and apply an action. Because the results can be on multiple pages I choose to use the search() method from model and regenerate the CDbCriteria and then call updateAll using this criteria.

Now I noticed a problem when trying to update the table using this criteria, when trying to generate the update comand it adds the join part after the Set part like this:

UPDATE table SET somecolumn=:value INNER JOIN other_table ON column=anothercolumn WHERE {condition} LIMIT {number}

There are two problems here:
1 . The limit should be ignored (when using join), there is an error '#1221 - Incorrect usage of UPDATE and LIMIT' (also in case ORDER BY is used it should also be ignored during join
2. JOIN part of the url should be before SET:

UPDATE table INNER JOIN other_table ON column=anothercolumn SET somecolumn=:value WHERE {condition}

@cebe
Member
cebe commented Jul 13, 2012

related to #124

@cebe cebe was assigned Aug 28, 2012
@cebe
Member
cebe commented Aug 28, 2012

The JOIN part should have been fixed by #525 now.

can you explain, what exactly is the problem with LIMIT #1221 does not tell anything about this?

@cebe
Member
cebe commented Aug 29, 2012

@resurtm got mails about your comments here, but can not see them, did you delete them? May I re-paste them here?

@resurtm
Contributor
resurtm commented Aug 29, 2012

@cebe sure, you can if you think that they related to the issue. :) I've deleted them because i came to thought that they a little bit not on the topic.

@cebe
Member
cebe commented Aug 29, 2012

As this is very reated to dao and also affects dao command creation so your comment was what I wanted for explaination ;)

The problem is that MySQL does not afford to run UPDATE query with JOIN and LIMIT in the same query.

mysql> UPDATE tbl_task_group AS tg SET tg.title='group3' WHERE tg.id=3 LIMIT 2;
Query OK, 0 rows affected
Rows matched: 1 Changed: 0 Warnings: 0

mysql> UPDATE tbl_task_group AS tg INNER JOIN tbl_task AS t ON t.task_group_id=tg.id SET tg.title='GROUP3', t.title='GROUP3' WHERE tg.id=3;
Query OK, 1 row affected
Rows matched: 3 Changed: 1 Warnings: 0

mysql> UPDATE tbl_task_group AS tg INNER JOIN tbl_task AS t ON t.task_group_id=tg.id SET tg.title='GROUP3', t.title='GROUP3' WHERE tg.id=3 LIMIT 2;
1221 - Incorrect usage of UPDATE and LIMIT
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.17-log |
+------------+
1 row in set

http://dev.mysql.com/doc/refman/5.0/en/update.html

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

@resurtm resurtm added a commit to resurtm/yii that referenced this issue Sep 21, 2012
@resurtm resurtm Fixes #957. 9cf2824
@cebe
Member
cebe commented Nov 21, 2012

Well, I think when you are not allowed to use limit in the query you should not set limit in the dbCriteria.
When re-using your criteria you should unset the value then.

$critiera->limit=-1;
$criteria->order='';

If there is a reason that this should be handled by yii, please explain in detail.

@cebe cebe closed this Nov 21, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment