I have 3 SQL tables Companies, Materials and Suppliers as follows. Tables
I need to insert values into Suppliers from a list which contains Company Name and Material Name as headers. However, I have multiple companies with the same name in the database and i need to add a new value into suppliers for each one of those companies.
For e.g. my list containes values ['Wickes','Bricks'] . I have this sql below to add a new entry into the suppliers table but since i have multple companies called 'Wickes' I'll get an error as the subquery will return more than 1 value.
INSERT INTO Suppliers(Id,CompanyId,MaterialId) VALUES (NEWID(), (SELECT Id FROM Companies WHERE Name = 'Wickes'),(SELECT Id FROM Materials WHERE Name = 'Bricks'))
Whats the best solution to get the Id of all the companies there are called 'Wickes' and then add vales into the suppliers table with that Id and the relevant material Id of 'Bricks'.
CodePudding user response：
INSERT INTO Suppliers(Id,CompanyId,MaterialId) VALUES (NEWID(), (SELECT distict Id FROM Companies WHERE Name = 'Wickes'),(SELECT distict Id FROM Materials WHERE Name = 'Bricks'));
CodePudding user response：
You can use INSERT () SELECT.. rather than INSERT () VALUES(), e.g
INSERT INTO Suppliers (Id, CompanyId, MaterialId)
SELECT NEWID(), c.Id, m.Id
FROM Companies AS c
CROSS JOIN Materials AS m
WHERE c.Name = 'Wickes'
AND m.Name = 'Bricks';
This will ensure that if you have multiple companies/materials with the same name, all permutations are inserted. Example on db<>fiddle
Although based on your image Suppliers.Id is an integer, so I think NEWID() is not doing what you think it is here, you probably just want:
INSERT INTO Suppliers (CompanyId, MaterialId)
SELECT c.Id, m.Id
FROM Companies AS c
CROSS JOIN Materials AS m
WHERE c.Name = 'Wickes'
AND m.Name = 'Bricks';
And let IDENTITY take care of the Id column in Suppliers.
As a further aside, I've also just noted that MaterialId is VARCHAR in your Suppliers table, that looks like an error if it is supposed to reference the integer Id column in Materials.
CodePudding user response：
If I understand rightly Companies are the suppliers and the Suppliers table is the one that says where you can buy each material from.
Why do you have duplicates? Do you have an account for different branches of Wickes for example? If they are really duplicates and you don't care which one you use a function like MIN() will do the job of ensuring that only one value is returned. If you have duplicates it would be a good idea to find a way of disactivating all except one. This will make is simpler for you everytime you want to deal with the supplier: minimum orders, chasing overdue orders, payments etc.
Also Companies.ID and Materials.ID should be foreign keys of the Suppliers table. It is also a good idea for the ID column to be auto-incrementing, which makes it easier to add new products as you do not need to specify the ID column.
If you cannot or do not want to modify the id column to auto-incrementing IDENTITY you can continue to use NEWID().
create table Companies( id INT PRIMARY KEY NOT NULL IDENTITY, name VARCHAR(25)); create table Materials( id INT PRIMARY KEY NOT NULL IDENTITY, name VARCHAR(25)); create table Suppliers( id INT PRIMARY KEY NOT NULL IDENTITY, CompanyId INT FOREIGN KEY REFERENCES Companies(id), MaterialId INT FOREIGN KEY REFERENCES Materials(id) );
INSERT INTO Companies (name) VALUES ('Wickes'); INSERT INTO Materials (name) VALUES ('Bricks');
INSERT INTO Suppliers ( CompanyId, MaterialId) SELECT c.Id, M.Id FROM Companies AS c CROSS JOIN Materials AS m WHERE c.Name = 'Wickes' AND m.Name = 'Bricks';
SELECT * FROM Companies; SELECT * FROM Materials; SELECT * FROM Suppliers; GOid | name -: | :----- 1 | Wickes id | name -: | :----- 1 | Bricks id | CompanyId | MaterialId -: | --------: | ---------: 1 | 1 | 1
db<>fiddle here
CodePudding user response：
INSERT INTO SUPPLIERS (ID, COMPANYID, MATERIALID) VALUES (NEWID(), (SELECT DISTINCT ID FROM COMPANIES WHERE NAME = 'Wickes'), (SELECT DISTINCT ID FROM MATERIALS WHERE NAME = 'Bricks'))