12/15/2023 0 Comments Mysql update set selectThe WITH VALUES is only needed when your Column is NullableĪnd you want the Default Value used for Existing Records. If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerateĪ Default-Contraint with a funny Name like: DF_SomeTa_SomeC_4FB7FEF6 WITH VALUES -Add if Column is Nullable and you want the Default Value for Existing Records. Mørch for this advice in the comments.Įxample technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.ĬONSTRAINT ĬONSTRAINT D_SomeTable_SomeCol -When Omitted a Default-Constraint Name is autogenerated.ĭEFAULT (0)-Optional Default-Constraint. SET optimizer_switch = 'derived_merge=off' Luckily, the optimizer_switch variable can be used to switch off this behaviour although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks. However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Table, so it doesn't count as the same table you're updating. The nested subquery in the FROM clause creates an implicit temporary Ugly for several reasons, including performance: UPDATE tbl SET col = ( If you absolutely need the subquery, there's a workaround, but it's UPDATE tbl AS aĪlternatively, try nesting the subquery deeper into a from clause. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead. If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. Maybe you can just join the table to itself In MySQL, you can't modify the same table which you use in the SELECT part. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question Update: This answer covers the general error classification. SET student.student_education_facility_id = cation_facility_id LEFT JOIN user_account teacher ON er_account_id = student.teacher_id If not, is there another way I can write this update sql to achieve the same affect?ĮDIT: I think I got it to work: UPDATE user_account student Is there any way I can force mysql to do the update? I am 100% positive the from clause will not be affected as the rows are updated. But since they are both using the same table, mysql spews out this error instead:ĮRROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause Ordinarily if teacher and student were in 2 different tables, mysql wouldn't complain. WHERE er_account_id = student.teacher_id AND er_type = 'ROLE_TEACHER' SET student.student_education_facility_id = ( Here's the query: UPDATE user_account student The problem is that I need to use a sub-query to derive the value for this column, and it depends on the same table. I'm trying to update a column's value in a bunch of rows in a table using UPDATE.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |