使用同一个表中的值更新表 [英] Update Table with values from same table

问题描述

SQL 问题.我把 system_id 作为 partent_id 与适当的 parent_id 是表 company_id.所以我正在尝试这个，但遇到了几个错误:

SQL Question. I put the system_id as the partent_id vs. the appropriate parent_id which is the tables company_id. So I was trying this, but am getting several errors:

update justmarket.companies e, (Select DISTINCT company_id from justmarket.companies where parent_id = system_id) c
set e.parent_id = c.company_id
where e.company_id = c.company_id

结束错误:

错误静态分析:

在分析过程中发现了 6 个错误.

6 errors were found during analysis.

需要一个表达式.(在位置 31 处的("附近) 出乎意料令牌.(在位置 31 附近的(") 找到了一个新语句，但没有找到它和前一个之间的分隔符.(靠近选择"位置32) 意外令牌.(靠近)"在位置 112) 意外标记.(在位置 114 的c"附近)找到了一条新语句，但没有定界符在它和上一个之间.(在位置 117 的set"附近)SQL查询:文档

An expression was expected. (near "(" at position 31) Unexpected token. (near "(" at position 31) A new statement was found, but no delimiter between it and the previous one. (near "Select" at position 32) Unexpected token. (near ")" at position 112) Unexpected token. (near "c" at position 114) A new statement was found, but no delimiter between it and the previous one. (near "set" at position 117) SQL query: Documentation

update justmarket.companies e，(选择 DISTINCT company_id fromjustmarket.companies where parent_id = system_id) c set e.parent_id =c.company_id 其中 e.company_id = c.company_id

update justmarket.companies e, (Select DISTINCT company_id from justmarket.companies where parent_id = system_id) c set e.parent_id = c.company_id where e.company_id = c.company_id

MySQL 说:文档

表:company_id company_name system_id parent_id1 姓名 1 55121 02 姓名2 52211 551213 姓名3 55444 55121

Table: company_id company_name system_id parent_id 1 Name1 55121 0 2 Name2 52211 55121 3 Name3 55444 55121

我想做什么company_id company_name system_id parent_id1 姓名 1 55121 02 姓名2 52211 13 姓名3 55444 1

What I'm looking to do company_id company_name system_id parent_id 1 Name1 55121 0 2 Name2 52211 1 3 Name3 55444 1

推荐答案

尝试使用显式 JOIN 语法而不使用子查询.

Try using explicit JOIN syntax without using a subquery.

UPDATE companies AS e
JOIN companies AS c ON e.company_id = c.company_id
SET e.parent_id = c.company_id
WHERE c.parent_id = c.system_id

这篇关于使用同一个表中的值更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！