4

Using SQL Server I am trying to find every unique value in column 1 of a table, and then insert a new row using that unique column 1 value and adding a column 2 value. Column 2 value will be the same every time.

To note: I could do this by pulling out the unique values from the database on column 1 and adding an insert for each but I have 1.6 million unique values in column 1 so it would be tiresome to write it that way.

Taking 2 unique value from column 1 to show this as an example:

select *
from dbo.SettopSubscription
where MacAddr = '0000000ee4b5'
   or MacAddr = '0000003a9667'

Results:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479

Now I want to add PackageId = 37 to every unique MacAddr value but so far no luck in writing something to find and add on only the unique values. As stated before I can do this easily by writing an insert into script for each MacAddr but that was take forever on 1.6 million MacAddr values.

Beginning view, same as above:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479

End result:

MacAddr      PackageId   
------------ ----------- 
0000000ee4b5    11
0000000ee4b5     3
***0000000ee4b5    37***
0000003a9667   241
0000003a9667   241
0000003a9667    11
0000003a9667   211
0000003a9667     8
0000003a9667  4411
0000003a9667  4412
0000003a9667  4479
***0000003a9667    37***

Thanks for the help ahead of time.

Tim
  • 73
  • 2
  • 7
  • 1
    *Now I want to add PackageId = 37 to every unique MacAddr* Do you want this to be part of filter or what ? – Pரதீப் Dec 21 '16 at 17:07
  • So just to clarify, you want to select distinct `column1` from `table1` and insert that value back into `table1` with various values in `column2`? Or do you want values from `column1` that only occur once so far, and insert those back into `table1` with values in `column2`? – gmiley Dec 21 '16 at 17:09
  • Not sure what you mean by a filter, I am newer to mssql but in other scripting I would most likely add the unique MacAddr values to a list then run a for or if statement to add new rows for every value of MacAddr in that list. Is that possible in mssql? – Tim Dec 21 '16 at 17:11
  • So when you find a unique value in column 1, you want to update column 2 of that value or you want to insert a row with that value and a value of column 2? – Amazigh.Ca Dec 21 '16 at 17:12
  • It might be of some help to give us a sample of what your table looks like initially, and a sample of the data that you want it to look like at the end. That should clear up any confusion. – gmiley Dec 21 '16 at 17:13
  • gmiley I would want the first instance of your question. Find the distinct/unique values in column1 and then add a new value for column2 using that distinct column1 value – Tim Dec 21 '16 at 17:14
  • Well distinct and unique are different things, that is why I asked for clarification. You seem to use them interchangeably. Providing sample before and after datasets should help in clearing that up. – gmiley Dec 21 '16 at 17:14
  • Amazigh.Ca. the latter. – Tim Dec 21 '16 at 17:16
  • I have edited to show a beginning and end result to the table for those 2 MacAddr values in column1, end result is to add a new row for each dinstinct/unique MacAddr with a value in column2 of '37'. – Tim Dec 21 '16 at 17:21
  • but I see the value 0000000ee4b5 from your first select is not unique, you have tow – Amazigh.Ca Dec 21 '16 at 17:23
  • My fault on that Amazigh.Ca, couldnt edit it again fast enough before you caught it. It is now updated. – Tim Dec 21 '16 at 17:25
  • So it looks like you just want to add another entry for each distinct `MacAddr` to create a new record with a `PackageId` of `37`? One for each `MacAddr`. I assume you also do not want to insert a duplicate if a record already exists for `PackageId` = `37`? – gmiley Dec 21 '16 at 17:28
  • you are correct gmiley. – Tim Dec 21 '16 at 17:29

4 Answers4

5

This will insert a record for each distinct MacAddr with a PackageId of 37 that does not already have a PackageId of 37:

insert into SettopSubscription (MacAddr, PackageId)
select distinct s1.MacAddr, 37 
from SettopSubscription s1
where not exists
(
    select s2.PackageId
    from SettopSubscription s2
    where s2.MacAddr = s1.MacAddr
    and s2.PackageId = 37
);
gmiley
  • 6,531
  • 1
  • 13
  • 25
  • This works as intended and adds the addition of not writing a duplicate row if PackageId = 'x' already exists. – Tim Dec 21 '16 at 18:19
2

To INSERT a new record into the SettopSubscription table for each unique value of MacAddr, with a PackageId of 37 (not inserting if there is already a record in the table for that combination of MacAddr and PackageId:

INSERT INTO SettopSubscription (MacAddr, PackageId)
SELECT DISTINCT s1.MacAddr, 37 
FROM dbo.SettopSubscription s1
LEFT JOIN dbo.SettopSubscription s2 ON s1.MacAddr = s2.MacAddr 
                                   AND s2.PackageId = 37
WHERE s2.MacAddr IS NULL
3N1GM4
  • 3,372
  • 3
  • 19
  • 40
  • This looks pretty close to what he was asking for, but I think I would put a condition on there to exclude any `MacAddr` that already has a `PackageId` of `37` - at least according to the confirmation in the comments. – gmiley Dec 21 '16 at 18:00
  • Works as intended, thank you! trying the other options to stop the possibility of writing duplicate rows. – Tim Dec 21 '16 at 18:02
  • Anyone have a solution to add to this that would eliminate the addition of the new row if PackageId = 37 was already in the table? – Tim Dec 21 '16 at 18:12
  • @Tim see my answer, if 3N1GM4 updates his soon I will remove mine. – gmiley Dec 21 '16 at 18:13
  • Yep, this will work too. If OP wants to accept yours as the answer since you mostly had it first, I am fine with that. Will upvote yours either way. =) – gmiley Dec 21 '16 at 18:46
0

try this:

insert into dbo.SettopSubscription (MacAddr,PackageId)   
select MacAddr, 'your_PackageId_Value'  from 
(select count(dbo.SettopSubscription.MacAddr ) nbr, MacAddr  from
dbo.SettopSubscription group by MacAddr ) tmp where tmp.nbr=1
Amazigh.Ca
  • 2,753
  • 2
  • 11
  • 8
0

It sounds like you want this:

declare @pid int
set @pid=37
insert into dbo.SettopSubscription (macaddr, packageid)
select distinct ss.macaddr, @pid
from dbo.SettopSubscription ss
where not exists (
    select * from dbo.SettopSubscription ss2 
    where ss2.macaddr=ss.macaddr and ss2.packageid=@pid)

For every unique macaddr in column 1, this is just inserting a record with the packageid you indicated and making sure the combination does not yet exist.

I added the variable under the assumption you might reuse the query. Putting a literal 37 in multiple locations can easily lead to data issues if on a future run, you only update the first use of the literal.

David Moreau
  • 148
  • 8