I have table for tenants
| Id | Name |
|---|---|
| 1 | Tenant1 |
| 2 | Tenant2 |
| 3 | Tenant3 |
And table for Pricing Tiers
| Id | Name | TenantId |
|---|---|---|
| 1 | PricingTier1 | 1 |
| 2 | PricingTier2 | 1 |
I need to create SQL script that get all tenants that don’t have pricing Tiers and add Pricing Tier with name Retail and TenantId
For example in this example it must be Tenant 2 and Tenant 3
So PricingTiers table will be like this
| Id | Name | TenantId |
|---|---|---|
| 1 | PricingTier1 | 1 |
| 2 | PricingTier2 | 1 |
| 3 | Retail | 2 |
| 4 | Retail | 3 |
I got tenants and try to insert to PricingTiers like this
INSERT INTO PricingTier (Name, TenantId, IsDeleted, CreationTime)
VALUES('Retail', (SELECT Id
FROM AbpTenants
WHERE NOT EXISTS(SELECT TenantId
FROM PricingTier
WHERE TenantId = AbpTenants.Id)),0, GETDATE())
But I get error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How I need to write script to create for every of those id’s new row in PricingTier table?