A simple query I imagine 2

[DomTrix]

Hi there, Im new to SQL and am finding it difficult to find the answers I need from books and searches so here goes:

I have table1, which has two columns: authorID and authorName. authorID is a unique identifier and primary key, authorName is a unique index. At present this table is empty.

I have table2, which amongst many other things, contains an authorName column. There are many records with many duplicated author names (it is a table of books).

My task:

Insert new authorNames from table2 into table1 when the authorName does not already exist in table1. Thus populating table1 with a list of unique authorNames.

Could anyone show me the query I need? I imagine it is reasonably simple, but finding the answer is proving a pain!

Thanks in advance

DT

 

[checkai]

Insert Into table1 (authorName)
Select AuthorName
From Table2
Where AuthorName Not In (select AuthorName from Table1)

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[hwkranger]

INSERT INTO TBL1
(AUTHOR_NAME)
SELECT
AUTHOR_NAME
FROM
TBL2
WHERE
author_name not in (SELECT Author_Name FROM TBL1)

Randall Vollen
National City Bank Corp.

 

[DomTrix]

Wow, thanks for the rapid replies. Great

 

[DomTrix]

Oh dang, it didnt work . It adds in duplicate records. I am guessing that it is only checking for duplicates in table1 BEFORE the query is run. At that stage, there are no records in the table so that would make sense. Am I wrong?

The code I have is:

INSERT INTO authorArtist
(authorName)
SELECT
productAuthorOrArtist
FROM
products
WHERE
(productAuthorOrArtist not in (SELECT authorName FROM authorArtist) )
AND
(productAuthorOrArtist != '-')

authorName is a column of table authorArtist.
productAuthorOrArtist is a column of table products.

Thanks again.

 

[jbenson001]

Are you saying that there are duplicate records in table 2, and that you only want to insert into table1 where not in table2?

 

[jbenson001]

if that is the case, in the subselct use

SELECT authorName FROM authorArtist group by authorname
or
SELECT DISTINCT authorName FROM authorArtist

 

[Catadmin]

Try this:

INSERT INTO authorArtist 
(authorName)
SELECT
 [b]Distinct[/b] productAuthorOrArtist
FROM
 products
WHERE 
  (productAuthorOrArtist != '-')

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[DomTrix]

Brilliant Catadmin thank you. Man, there's so many useful keywords, got me some learning to do!

ty all

 

[Pattycake245]

Try this:

insert authorArtist(authorName)
select a.productAuthorOrArtist
from (select distinct productAuthorOrArtist from products where productAuthorOrArtist != '-') a
left join authorArtist b on a.productAuthorOrArtist=b.authorName
where b.authorName is null

Tim

 

[Catadmin]

You're welcome.

Keywords definately make all the difference. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???