SQL插入语句不起作用 [英] SQL Insert Statement not working

问题描述

我有两个表，我需要从一个表到另一个表获取数据列。我在一个表中创建了一个名为LName的列。在另一个表中，列LName中包含数据，而我的代码不起作用。我试图根据用户ID插入数据。我做错了什么？

  INSERT   INTO 表20（LName）
 
  SELECT  LName  FROM 表21 
 
  WHERE  LName = User_ID 

表20布局

<前lang =HTML> User_ID LName颜色车
10001红色福特
10002蓝色福特

表21布局

 User_ID LName 
 10001 Mike 
 10002 James 

表20结束结果



<前lang =HTML> User_ID LName颜色车
10001 Mike Red Ford
10002 James Blue Ford

解决方案

您是否真的尝试更新数据，而不是插入新行？我的意思是：

 更新表20 
  SET  LName =（ SELECT  t21.LName 
  FROM 表21 t21 
  WHERE  t21.User_ID = Table20.User_ID）

INSERT语句将用于您的情况'即将向表中添加新行。然后，UPDATE再次用于更新现有行的列中的数据。



上述语句使用相关子查询来更新LName。这意味着表20中的每一行都是循环的，对于每一行，语句尝试使用User_ID作为键来查找相应的LName。从相关子查询中使用的别名可以看出，User_Id是从外部语句（Table20）中获取并与内部语句（别名t21）匹配。



来自OP：

我使用了你给的代码，我执行了它，它说771行受到了影响。我刷新了表格，LName列中没有数据。



尝试以下查询

  SELECT  T20.User_ID，T21.LName，T20.Color，T20.Car 
  FROM 表20 T20 
  INNER   JOIN 表21 T21  ON  T20.User_ID = T21.User_ID; 

该查询是否显示行并正确地将LName提取到结果集中？如果没有，那么表之间的用户ID不匹配。

您需要使用join进行更新，例如

 更新 a 
  SET  a.LName = b.LName 
 来自表20 a 
  INNER   JOIN 表21 b 
  ON  a.User_ID = b.User_ID 

I have two tables and I needed to get a data column from one table to another. I have created a column named LName in one table. In the other table the column LName has data in it and my code just won't work. I am trying to get the data to insert based on User ID. What am I doing wrong?

INSERT INTO Table20 (LName)

SELECT LName FROM Table21

WHERE LName = User_ID

Table20 Layout

User_ID       LName         Color           Car
10001                        Red            Ford
10002                        Blue           Ford

Table21 Layout

User_ID       LName        
10001         Mike
10002         James

Table20 End Result

User_ID       LName         Color           Car
10001         Mike           Red            Ford
10002         James          Blue           Ford

解决方案

Are you actually trying to update the data, not insert new rows? I mean something like:

UPDATE Table20
SET LName = (SELECT t21.LName 
             FROM Table21 t21
             WHERE t21.User_ID = Table20.User_ID)

INSERT statement would be used in a situation where you're about to add new rows to the table. UPDATE then again is used for updating data in columns for existing rows.

The statement above uses a correlated subquery to update the LName. This means that each of the rows in Table20 is looped and for each row the statement tries to find corresponding LName using the User_ID as a key. As you can see from the aliases used in the correlated subquery, the User_Id is fetched from the outer statement (Table20) and matched with the inner statement (alias t21).

From OP:
I used the code you gave and I Executed it and it said that 771 row affected. I did a refresh of the table and there is no data in column LName.

Try the following query

SELECT T20.User_ID, T21.LName, T20.Color, T20.Car
FROM Table20 T20 
     INNER JOIN Table21 T21 ON  T20.User_ID = T21.User_ID;

Does that query show the rows and fetch the LName to the result set correctly? If not then the user id's are not matching between the tables.

you need update with join, for example

UPDATE a
  SET a.LName = b.LName
from Table20  a
  INNER JOIN Table21  b
    ON a.User_ID        = b.User_ID

这篇关于SQL插入语句不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！