Update Rows in one table based on another table

Question

Update Rows in one table based on another table

sweetlisa1217

SSC Enthusiast

Points: 114
More actions
November 3, 2021 at 9:46 pm

#3947696

Posted this in a BI Forum but am curious as to how to do this in SQL. I would prefer to do it in SQL if it's not too complex.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Lisa,

Welcome! Since you're new, I created the CREATE and INSERT scripts for your question. (Posting screenshots doesn't help the folks on here recreate your scenario, so it makes it less likely that you'll get an answer.)

CREATE TABLE Invoices (
InvoiceID INT,
CustomerID CHAR NOT NULL
CONSTRAINT pkInvoices
PRIMARY KEY (InvoiceID));

GO
CREATE TABLE Users (
UserID INT PRIMARY KEY,
CustomerID CHAR,
MaxRows TINYINT);
GO

INSERT INTO Invoices VALUES (123456,'A'),
(456789,'B'),(678910,'A');

INSERT INTO Users VALUES (1234,'A',30),(23456,'B',5);

Are you saying that each Customer is assigned to a pool/collection of users or just one? Can a user have a constrained number of Customers he is assigned? (that's what MaxRows is for, right?) If so, this should get the first N invoices for each UserID.

SELECT u.UserID, u.CustomerID, u.MaxRows, ca.InvoiceID
FROM Users u
CROSS APPLY (SELECT TOP(u.MaxRows) i.InvoiceID, i.CustomerID
FROM Invoices i
WHERE i.CustomerID = u.CustomerID) ca
ORDER BY u.UserID, u.CustomerID;

If you have to churn through a ton of invoices, and assign them to users, I would probably use a cursor and do a TOP N to get the number of invoices that a user can "accept", and then assign those - and then you can just loop through chunks of records (using TOP(n)) and updating them to assign to a user (just get that user's MaxRows, and add til full. Then go to the next user. Process until all users have been assigned Invoices and are maxxed out or you're out of invoices to assign.

That's how I would approach it... Maybe someone else on here has a better idea, though.

sweetlisa1217 SSC Enthusiast Points: 114 More actions · Answer 2

Pietlinden, thank you so much for taking the time to create the code and post your thoughts on a solution. Very much appreciated, next time I will include code. Thank you, I'm going to give this a try!

sweetlisa1217 SSC Enthusiast Points: 114 More actions · Answer 3

This is beautiful! It worked like a charm for me. Thank you so much! As usual, I was over complicating it a bit. I didn't realize you could select Top based on a value from another table. You've helped me alot! This is a nice clean solution and perfect for what I need.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Glad it helped!

Good luck on the learning journey! Here's a really good article about posting questions - short and to the point... should be required reading. It's maybe a five minute read, and definitely worth it.

Forum Etiquette: How to post data/code on a forum to get the best help