-3

I have a table with users, a table with levels, a table for submitted orders and processed orders.

Here's what the submitted orders looks like:

OrderId UserId  Level_Name  Discounted_Price    Order_Date              Price
1       1       OLE Core            0           2020-11-01 00:00:00.000 19.99
2       1       Xandadu             1           2020-11-01 00:00:00.000 0
3       2       Xandadu             0           2020-12-05 00:00:00.000 5
4       1       Eldorado            1           2021-01-31 00:00:00.000 9
5       2       Eldorado            0           2021-02-20 00:00:00.000 10
6       2       Birmingham Blues    NULL        2021-07-10 00:00:00.000 NULL

What I am trying to do:

UserId 2 has an order for Birmingham Blues, they have already ordered Eldorado and so qualify for a discount on their Birmingham Blues order. Is there a way to check the entire table for this similarity, and if it exists update the discounted price to a 1 and change the price to lets say 10 for the Birmingham Blues order.

EDIT: I have researched the use of cursors, which I'm sure will do the job but they seem complicated and was hoping a simpler solution would be possible. A lot of threads seem to also avoid using cursors. I also looked at this question: T-SQL: Deleting all duplicate rows but keeping one and was thinking I could potentially use the answer to that in some way.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jamie Talbot
  • 1
  • 1
  • I am very new to SQL and am merely trying to gauge if something like this is possible and also feasible. And if so confirmation that I'm looking in the right area: i have seen Cursors, which seem like they might work, but I dont fully understand them yet. – Jamie Talbot Jan 25 '22 at 21:01
  • Its totally possible and doesn't require cursors... but you're expected to have done a bunch of research, e.g. tutorial sites etc before posting here. You should be posting here when stuck on a specific aspect of a query, not when you have no idea where to start. – Dale K Jan 25 '22 at 21:03
  • Perhaps **you** know what "for this similarity" means but the database engine does not. What logic determines if a given value for "Level_Name" is similar to another such that a discount is warranted? – SMor Jan 25 '22 at 21:11
  • Any order where the level name is birmingham blues, and the user has already ordered the eldorado level qualify for a discount. – Jamie Talbot Jan 25 '22 at 21:18
  • How do you calculated the price - or does "change the price to lets say 10" imply pick the previous order value? A random value? Always 10? – Stu Jan 25 '22 at 21:24
  • @Stu for now it is a fixed price, so always 10. – Jamie Talbot Jan 25 '22 at 21:26
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Feb 03 '22 at 23:02

1 Answers1

0

Based on your description and further comments, the following should hopefully meet your requirements - updating the row for the specified User where the values are currently NULL and the user has a qualifying existing order:

update s set
  s.Discounted_Price = 1, 
  Price = 10
from submitted_Orders s
where s.userId=2 
  and s.Level_Name = 'Birmingham Blues' 
  and s.discounted_Price is null
  and s.Price is null
  and exists (
      select * from submitted_orders so 
      where so.userId = s.userId
        and so.Level_name = 'Eldorado'   
        and so.Order_Date < s.OrderDate
  );
Stu
  • 30,392
  • 6
  • 14
  • 33