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