Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

How do I add new rows to a new table where there isn't an existing row that matches the foreign key?

I want to create records in a table with a value for every matching record in another table  but only if there isn't already a record.
So for the following :
Customers                  Locations      
CustomerID                  CustomerID      Location
1                  2      Unknown
2                  3      England
3                  4      Spain
4                        
5                        

I would like for the sql command to end up with the tables looking like this:
Customers                  Locations      
CustomerID                  CustomerID      Location
1                  1      Unknown
2                  2      Unknown
3                  3      England
4                  4      Spain
5                  5      Unknown
0
dgloveruk
Asked:
dgloveruk
  • 2
2 Solutions
 
UnifiedISCommented:
I'm not following your table layouts but a general way to do this would be:
insert into tableA
(SELECT columns from tableB
WHERE key not in (SElect key from tableA))
0
 
Kevin CrossChief Technology OfficerCommented:
Something like this.  You can replace with actual columns/values you want.
INSERT INTO Locations (CustomerID, Location)
SELECT CustomerID, 'Unknown'
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Locations)
 
-- OR
INSERT INTO Locations (CustomerID, Location)
SELECT CustomerID, 'Unknown'
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Locations l WHERE l.CustomerID = c.CustomerID)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Guess, we had the same thought, UnifiedIS. :)
0
 
dgloverukAuthor Commented:
Thanks guys, I split the points on this occasion because you actually seemed to answer simultaneously!
Thanks again,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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