Smarter SQL UPDATE between two tables (bill and bill rows)

Question

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)

That `UPDATE` is flawed. It isn't correlated (though oddly the subquery within the subquery is), it has a `GROUP BY` (which will result in multiple rows) and then you have a `TOP 1` with no `ORDER BY` meaning that you are going to assign an *arbitrary* value to *every* row in `Bills`. It is very unlikely to do what you think it does, or what you want it to do. — Thom A, Apr 14 '22 at 17:46
It works fine @Larnu meaning that it does what I presented in the picture. — sports, Apr 14 '22 at 17:51
You said this: "it has a GROUP BY (which will result in multiple rows)". The first "having" assures that the result will be exactly one row, not multiple rows. — sports, Apr 14 '22 at 18:02
https://stackoverflow.com/questions/15245322/how-does-select-top-works-when-no-order-by-is-specified — SOS, Apr 15 '22 at 01:31

score 0 · Answer 1 · answered Apr 14 '22 at 17:54

0

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
)

answered Apr 14 '22 at 17:54

DhruvJoshi

17,041
6
41
60

Kazi Mohammad Ali Nur Romel · Answer 2 · 2022-04-14T18:43:22.170

0

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
)

edited Apr 14 '22 at 18:43

answered Apr 14 '22 at 18:16

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

Excellent and easy to read – sports Apr 14 '22 at 18:29
Although i had to use `select top 1` – sports Apr 14 '22 at 18:33
Thanks @sports. But since I have used max() top 1 should not be needed. Please try without it. – Kazi Mohammad Ali Nur Romel Apr 14 '22 at 18:35
I tried with max as you suggested but it throws: `Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` – sports Apr 14 '22 at 18:37
hmmm sorry your answer is not working... i checked and it updated wrong :( it gave a value to those bills that have mixed products – sports Apr 14 '22 at 18:40
Would you please share some sample data in text format or a db-fiddle link. So that, I can test for myself. – Kazi Mohammad Ali Nur Romel Apr 14 '22 at 18:42
@sports my mistake. I should have removed ProductId from group by clause. Now please check. – Kazi Mohammad Ali Nur Romel Apr 14 '22 at 18:43
Ok, i will add sample data – sports Apr 14 '22 at 18:50
I can't use max(ProductId) if its not included in the group by, but actually the group-by isn't necessary, right? – sports Apr 14 '22 at 19:00
You need ProductId for a BillId where all the ProductIds are same. So group by BillId should do the work. – Kazi Mohammad Ali Nur Romel Apr 14 '22 at 22:25

score 0 · Answer 3 · answered May 12 '22 at 19:20

I think this update might be more efficient than what I initially posted:

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

If we read from inside to outside...

First we have this:

        select 
            BillsRows.BillId, 
            BillsRows.ProductId
        from BillsRows
        group by 
            BillsRows.BillId,
            BillsRows.ProductId

(This returns a virtual table with two columns: BillId and ProductId)

Then, I group again, by BillId, and stick with those with count(*) = 1

    select 
        BillsRowsGrouped.BillId
    from (...) as BillsRowsGrouped
    group by BillsRowsGrouped.BillId
    having count(*) = 1

Finally, I update using the top 1 result from BillsRows and making sure BillId is in the previous query:

update Bills
set 
Bills.ProductId = (
    select 
        top 1 ProductId
    from BillsRows
    where BillsRows.BillId = Bills.BillId
)
where BillId in (...)

Smarter SQL UPDATE between two tables (bill and bill rows)

3 Answers3