I have these two SQL tables:

Bills (BillId, Folio, Date, ProductId)
BillsRows (BillRowId, BillId, ProductId, Lot, Quantity, Price)

• ProductId is optional in Bills
• ProductId is mandatory in BillsRows
• If all the rows of a bill have the same ProductId then the bill will have that ProductId
• If there are mixed ProductId within a bill, then the bill will have ProductId = NULL

Visual example:

I have created the following SQL UPDATE but I'm not sure it is efficient:

update Bills
set ProductId = (
    select top 1 ProductId
    from BillsRows
    group by ProductId, BillId
    having count(*) = (select count(*) from BillsRows where BillId = Bills.BillId)
    and BillId = Bills.BillId
)

The fact that I'm counting all the rows is what makes me think it is not quite efficient.

Is there any smarter alternative than this?

Update: added two samples

Sample 1: Bill should be updated to ProductId=1 because all of the rows have ProductId=1

insert into Bills (BillId, Folio, Date, ProductId) values (1, 324, '2022-04-14', null)
insert into BillsRows (BillRowId, BillId, ProductId) values (1, 1, 1111)
insert into BillsRows (BillRowId, BillId, ProductId) values (2, 1, 1111)
insert into BillsRows (BillRowId, BillId, ProductId) values (3, 1, 1111)

Sample 2: Bill should be updated to ProductId=NULL because it has different products

insert into Bills (BillId, Folio, Date, ProductId) values (2, 325, '2022-04-14', null)
insert into BillsRows (BillRowId, BillId, ProductId) values (4, 2, 1111)
insert into BillsRows (BillRowId, BillId, ProductId) values (5, 2, 2222)
insert into BillsRows (BillRowId, BillId, ProductId) values (6, 2, 3333)

CodePudding user response：

Maybe something like this

update B
set B.ProductId =R.ProductID
from Bills B inner join
BillsRows R on R.BillID=B.BillID
where not exists
(
    select 1
    from BillsRows BR where BR.BillId = R.BillId 
    and B.ProductID<> BR.ProductId
)

CodePudding user response：

Please try below query. It should be faster.

update Bills
set ProductId = (
    select max(ProductId)
    from BillsRows
    group by BillId
    having count(distinct ProductId) =1
    and BillId = Bills.BillId
)