个人中心
gpt4 book ai didi

SQL Query - Insert values from two columns in another table into one column(SQL查询-将另一个表中两列的值插入到一列中)

转载 作者：bug小助手 更新时间：2023-10-24 23:25:07 25 4
gpt4 key购买 nike



Can anyone please help me with an SQL query which will allow me to populate the real_name column in table 1 with values from both first_name and second_name columns in table 2 by referencing the usernames.

有谁可以帮助我执行一个SQL查询，它允许我通过引用用户名使用表2中的First_Name和Second_Name列中的值来填充表1中的REAL_NAME列。


TABLE 1

表1




























audit_username real_name
AHolland
THardy
LGentle
BGreen


TABLE 2

表2

































second_name first_name username
Holland Andrew AHolland
Hardy Tom THardy
Gentle Lauren LGentle
Green Barry BGreen

更多回答

Is table1 empty? Or how to determine names from second column match row?

表1是空的吗？或者如何从第二列匹配行中确定姓名？

优秀答案推荐

How about

怎么样


UPDATE TABLE1 T1 SET 
real_name=((SELECT CONCAT(firstname, ' ',  lastname) FROM TABLE2 WHERE username = T1.audit_username ))


It's unclear to me whether you just want to execute a query or to really change the data in table1.
If you want the query only:

我不清楚您是只想执行查询，还是想真正更改表1中的数据。如果您只想要查询：


SELECT
  t1.audit_username,
  CONCAT(t2.first_name, ' ', t2.second_name) AS real_name
FROM
  table1 t1 INNER JOIN table2 t2
    ON t1.audit_username = t2.username;

If you want to change the data:

如果您想要更改数据：


UPDATE 
  table1 t1 INNER JOIN table2 t2 
    ON (t1.audit_username = t2.username)
SET 
  t1.real_name = CONCAT(t2.first_name, ' ', t2.second_name);

See here this will work correctly.

请看这里，这将正常工作。


Note: Take care to use the correct syntax to update the table! The syntax provided in this answer is invalid for a MySQL DB.

注意：请注意使用正确的语法更新表！此答案中提供的语法对于MySQL数据库无效。


I recommend to read some documentations, for example this one about the correct syntax for such update commands.

我建议阅读一些文档，例如这篇关于此类更新命令的正确语法的文档。



You can use an UPDATE statement with a JOIN to achieve this. Here's an example of how you can do it:

您可以使用带有联接的UPDATE语句来实现这一点。下面是一个如何做到这一点的例子：


UPDATE table1 
SET real_name = CONCAT(table2.first_name, ' ', table2.second_name)
FROM table1
INNER JOIN table2 ON table1.audit_username = table2.username;

This SQL query will join table1 and table2 on the username field, and then update the real_name field in table1 with the first_name and second_name fields from table2, separated by a space.

该SQL查询将在用户名字段上联接Table1和Table2，然后使用Table2中的First_Name和Second_Name字段更新Table1中的REAL_NAME字段，用空格分隔。



To insert values from two columns in one table into a single column in another table using SQL, you can use the INSERT INTO statement along with a SELECT query to retrieve the values from the source table. Here's a general syntax for such an operation:

要使用SQL将一个表中两列中的值插入另一个表中的单个列中，可以使用INSERT INTO语句和SELECT查询从源表中检索值。下面是此类操作的一般语法：


Example-

示例：


INSERT INTO destination_table (destination_column)
SELECT source_column1 || ' ' || source_column2
FROM source_table;

INSERT INTO Destination_TABLE(Destination_Column)SELECT SOURCE_COLUNN1||‘’||SOURCE_COLUNN2 FROM SOURCE_TABLE；


更多回答

INSERT is not applicable here as the questioner wishes to update existing rows in Table1

此处不适用INSERT，因为提问者希望更新表1中的现有行

25 4 0
文章推荐： Transition of wave animation in css from left to right(CSS中的波浪动画从左到右的过渡)
文章推荐： Adding JDK path from WSL2 Ubuntu to IntelliJ IDE frozen the screen(添加从WSL2 Ubuntu到IntelliJ IDE的JDK路径冻结屏幕)
文章推荐： How can I add a TITLE to my summarise output in R?(如何将标题添加到R中的摘要输出？)
文章推荐： What happens to child process once the shell command is completed?(一旦外壳命令完成，子进程会发生什么情况？)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com