oracle 38104 columns reference in the on clause connot be update
时间: 2023-12-14 19:40:26 浏览: 32
这个错误通常是由于尝试在ON子句中更新一个被引用的列所导致的。在Oracle中,ON子句只能用于JOIN操作,而不能用于更新操作。在更新语句中,应该使用WHERE子句来指定更新的行。
例如,如果您有以下更新语句:
```
UPDATE table1 t1
JOIN table2 t2 ON t1.id = t2.id
SET t1.column1 = 'value'
```
您应该将其更改为:
```
UPDATE table1 t1
SET t1.column1 = 'value'
WHERE t1.id IN (SELECT id FROM table2)
```
这样就可以避免在ON子句中引用被更新的列而导致的错误。
相关问题
dependent on columns in GROUP BY clause
"dependent on columns in GROUP BY clause"是指在SQL查询中,如果SELECT列表、HAVING条件或ORDER BY列表引用了未在GROUP BY子句中命名的非聚合列,则会出现这个问题。 这是因为根据SQL-92标准和更早版本,不允许这样的查询。换句话说,如果你在GROUP BY子句中指定了一组列进行分组,那么SELECT列表、HAVING条件和ORDER BY列表中的列必须是这组列的聚合函数或已在GROUP BY子句中命名的列。如果有未命名的非聚合列,则需要重新调整查询以符合SQL语法要求。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* [MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona...](https://download.csdn.net/download/weixin_38742656/13685329)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
- *2* *3* [MySQL:dependent on columns in GROUP BY clause解决方案](https://blog.csdn.net/fengyuzaitu_126_com/article/details/118339840)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
[ .reference_list ]
sql which is not functionally dependent on columns in GROUP BY clause
引用和讨论了SQL中的一个错误消息:“Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause”。这个错误消息意味着在使用GROUP BY语句时,ORDER BY子句中的表达式不在GROUP BY子句中,并且包含了一个在GROUP BY子句中不是函数依赖的非聚合列。
为了解决这个问题,你可以通过更改MySQL配置来修改sql_mode。引用中提供了两种修改sql_mode的方法。一种方法是使用vim编辑器打开my.cnf配置文件,找到sql_mode参数并将其设置为不包含ONLY_FULL_GROUP_BY选项。另一种方法是使用命令行输入sql_mode参数,并将其设置为不包含ONLY_FULL_GROUP_BY选项。在修改完配置文件或输入命令后,需要重新启动MySQL服务器才能使修改生效。
总结来说,解决这个错误的方法是修改MySQL的sql_mode配置,将其设置为不包含ONLY_FULL_GROUP_BY选项。这样就可以避免在使用GROUP BY语句时出现“not functionally dependent”错误。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* [MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona...](https://download.csdn.net/download/weixin_38742656/13685329)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
- *2* *3* [SQLSyntaxErrorException: which is not functionally dependent on columns in GROUP BY clause](https://blog.csdn.net/qq_45350014/article/details/123920313)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
[ .reference_list ]