通过从另一张表复制单列的单行来在一个表中插入记录

admin

通过从另一张表复制单列的单行来在一个表中插入记录

sql

我有一张表SEND，只有一列Aname：

Aname   
------
GREAME  
PETER

另一个RECEIVE有2列的表格：

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'

现在，如上所示，我只想将表中RECEIVE的Aname列插入到新表中，SEND而在第二列中，我将插入一个新值。

另外，我希望不能在RECEIVE表中插入重复的值：

我要这个：

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

我正在使用此查询：

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

该查询从SEND表中同时获取名称GREAME＆PETER并将其插入RECEIVE表中，但是我希望仅选择GREAME，而不选择PETER。

我该怎么做？

它从表SEND接收所有行，并将它们插入到表RECEIVE中，但是我希望只插入一行。所以我希望查询像这样，但是它给出语法错误：

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')

阅读 184

2021-07-01

共1个答案

admin

一个语句中不能有 两个WHERE子句 ：

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')

您需要将第二部分更改为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')

2021-07-01