通过从另一个表复制单一列​​的单一行插入记录在一个表 [英] insert record in one table by copying single row of a single column from another table

问题描述

我有一个表发送有一列 Aname ：

  Aname
------
GREAME
PETER
 

另一个表接收 2列：

  Aname权限
-----------------------------------------------
GREAME（从表发送复制）我自己的价值将在这里插，
                                    在插入查询中指定

PETER（从表发送复制）我自己的价值将在这里插，
                                    在插入查询中指定
 

现在，如上图所示，我要插入到新表接收仅 Aname 从表列发送，并在第二列我会插入一个新的价值。

另外，我想，没有重复的值可以插入接收表：

我想这样的：

  Aname权限
---------------------
GREAME播放器
GREAME播放器---无法插入
GREAME售货员---可以插入
 

我使用这个查询：

 插入接收（Aname，许可）
  选择Aname，'选择'
  从SEND
  其中，不存在
     （选择接收来自*其中Aname ='GREAME'和权限=选择）
 

此查询需要两个名字GREAME和放大器;彼得从发送表，并将其插入到接收表，但我想，只有GREAME是捡来的，不彼得。

我该怎么办呢？

这需要所有行从表发送，并且将它们插入表接收，但我想只有一个行被插入。所以我想查询是这样的，但它给语法错误：

 插入接收（Aname，许可）
  选择Aname，'选择'
  从SEND
  WHERE Aname ='GREAME'-------------此行给出了错误
  其中，不存在
    （选择接收来自*其中aname ='GREAME'和权限=选择）
 

解决方案

您不能有双WHERE子句在一条语句：

 插入接收（Aname，许可）
  选择Aname，'选择'
  从SEND
  WHERE Aname ='GREAME'＆LT; ===其中＃1
  其中，不存在＆LT; ===其中＃2
    （选择接收来自*其中aname ='GREAME'和权限=选择）
 

您需要将第二部分更改为AND：

 插入接收（Aname，许可）
  选择Aname，'选择'
  从SEND
  WHERE Aname ='GREAME
  和NOT EXISTS
    （选择接收来自*其中aname ='GREAME'和权限=选择）
 

I have a table SEND with one column Aname:

Aname   
------
GREAME  
PETER   

Another table RECEIVE with 2 columns:

Aname                              Permission
-----------------------------------------------
GREAME (copied from table SEND)    'my own value will be inserted here,
                                    specified in insert query'

PETER  (copied from table  SEND)    'my own value will be inserted here,
                                    specified in insert query'

Now as shown above, I want to insert into a new table RECEIVE only the Aname column from table SEND and in the second column I would insert a new value.

Also, I want that no duplicate values could be inserted in RECEIVE table:

I want this:

Aname   Permission
---------------------
GREAME  PLAYER
GREAME  PLAYER --- COULD NOT BE INSERTED
GREAME  SALESMAN   --- COULD BE INSERTED

I am using this query :

insert into receive(Aname, Permission)
  select Aname , 'Select' 
  from SEND
  where not exists
     (select * from RECEIVE where Aname = 'GREAME' and Permission = 'select')

This query takes both the names GREAME & PETER from the SEND table and inserts them into the RECEIVE table, but I want that only GREAME is picked up, not PETER.

How do I do it?

IT TAKES ALL THE ROWS FROM TABLE SEND, and inserts them in table RECEIVE, but I want that only one row gets inserted. So I want the query to be like this but it gives syntax error:

insert into RECEIVE(Aname , Permission)
  select Aname , 'select' 
  from SEND
  WHERE Aname = 'GREAME'  -------------this line gives error
  where not exists
    (select *  from RECEIVE where aname = 'GREAME' and Permission = 'select')

解决方案

You can't have two WHERE clauses in one statement:

insert into RECEIVE(Aname , Permission)
  select Aname , 'select' 
  from SEND
  WHERE Aname = 'GREAME'   <=== WHERE #1
  where not exists         <=== WHERE #2
    (select *  from RECEIVE where aname = 'GREAME' and Permission = 'select')

You need to change the second part to an AND:

insert into RECEIVE(Aname , Permission)
  select Aname , 'select' 
  from SEND
  WHERE Aname = 'GREAME'  
  AND not exists
    (select *  from RECEIVE where aname = 'GREAME' and Permission = 'select')

这篇关于通过从另一个表复制单一列​​的单一行插入记录在一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！