Q:

Update value based in id not in select

Okay, this one could be tricky and I would appreciate your inputs :-)
I have a database with orders. For every order, there's x number of order lines.
Every order line is a ticket and every ticket needs a unique sequence number. The sequence number is unique for every product.
In the order lines table, there will be an entry for every ticket, so that every ticket can have a unique sequence number.
When a purchase has been completed I want to update the database and insert the sequence number. The problem is that I want to update order lines based on order id and product id. But the sequence number that needs to be inserted based on the highest sequence number already existing for that product id.

Imagine the above scenario. We have two orders, and these are the order lines correspondent to them. I now want to insert a row which would have the following data: Id = 8, SeqNumber = 5, ProductNo = 2, OrderNo = 3, CustomerNo = 3.
I need to do this in an update statement, since the row will already exist, but with the sequence number 0.
How would I go about doing this? I would want to update the order lines where OrderNo = 3 and ProductNo = 2. But I would want to retrieve the highest sequence number for all order lines with ProductNo = 2.
Thank you in advance. And let me know if you need more information :-)
EDIT: One extra thing. I would need to be able to do this, for more than one order line at a time, without them getting the same sequence number.

A:

I understood that your new rows are already in the table with SeqNumber=0
If you update multiple rows at a time you will need an internal order for the new rows:
SELECT Id,ProductNo,row_number() OVER (PARTITION BY ProductNo ORDER BY Id) as Offset
FROM YourTable yt
WHERE SeqNumber=0

Also you will need the Last Sequence Number per ProductNo:
SELECT ProductNo, Max(SeqNumber) as LastSeqNumber FROM YourTable GROUP BY ProductNo

In the end you take all this together and Update the new rows with the Last Sequence Number + Internal Offset:
UPDATE yt SET SeqNumber=sn.LastSeqNumber+tmp.Offset
FROM YourTable yt
    JOIN (SELECT Id,ProductNo,row_number() OVER (PARTITION BY ProductNo ORDER BY Id) as Offset
            FROM YourTable yt
            WHERE SeqNumber=0) tmp ON tmp.Id=yt.Id
    JOIN (SELECT ProductNo, Max(SeqNumber) as LastSeqNumber FROM YourTable GROUP BY ProductNo) sn ON sn.ProductNo=yt.ProductNo

