• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2346
  • Last Modified:

insert into distinct

Id like to use a query

insert into `domainList` (`domain`)  select distinct `domain` from `ListAll`;

it works first time, but when I exectue it a second it tells me values are not unique.

I know why its happening, but Id like it to just import the values, do nothing if it already exists, but insert it if it doesnt exist.

Any ideas?

Tony
0
tonelm54
Asked:
tonelm54
  • 3
  • 2
  • 2
1 Solution
 
discon0Commented:
Would this work?

insert into `domainList` (`domain`)  
select distinct `domain` from `ListAll` where `domain` not in (select `domain` from `domainList`)
0
 
NeoTeqCommented:
A faster version, without the subquery:

INSERT INTO `domainList` (`domain`)
SELECT DISTINCT `ListAll`.`domain`
FROM `ListAll` LEFT JOIN `domainList` ON `ListAll`.`domain` = `domainList`.`domain`
WHERE `domainList`.`domain` IS NULL
0
 
discon0Commented:
NeoTeq is right, his version is faster. :)
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the records:

INSERT INTO `domainList` (`domain`)
SELECT DISTINCT `domain`
FROM `ListAll`
  ON DUPLICATE KEY UPDATE `domain` = `domain`;


http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
0
 
NeoTeqCommented:
Oh wow why did they come up with that!?!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as marked in the docs:
<...>added in MySQL 4.1.0<...>
looks like you missed something :)
0
 
NeoTeqCommented:
LOL... No I didn't say when, I said why :P
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now