Column in INSERT query needs to increment if matching data on rows is different

Question

I tried to simplify what I am trying to do. This is for work, we sell metal. When an order is placed material, coating, masking, etc. is chosen. Then someone allocates material for the order. They want to create 2 programs. One where a user will enter in an order number, it will verify it is a correct order number. It will then look at 4 tables with existing data. It needs to take certain specs requested from those 4 tables and put them in 4 other tables that are currently empty. Bottom line is they want to find out what was allocated for the order. If that particular combination has already been requested on a different order number they just want to add a 1 to times_used column. End result would be a different program that looks through these new tables. They want to be able to determine what needs to be allocated for an order by looking at what was previously allocated on other orders. Is there any way this can be one program and not two? The information is already in the tables I don' know why I couldn't just do this with 1 program.

Answer 1

This is where views come in handy, and an understanding of relational databases. You can store each entry in a table dedicated for that. Each entry can have its own PK value that lets you differentiate between it. However, each entry can refer to a separate table to identify the Customer, Order, LineSeq. If you need to insert an entry for a Cus+Ord+LS that doesn’t exist yet, you create a new one, with its own PK value. Then you create a view which joins those two tables, including a COUNT_BIG(*) aggregate to count how many times they’re used. You could even index the view so that the values are worked out ahead of time.

To use this the way you want, you could put an “instead of trigger” on your view that handles the inserts/deletes into your entries table. That’s essentially a procedure that you interact with by doing inserts into a view, even though your view can’t handle inserts.

Answer 2

You need to get your app to do it instead of your SQL Query, but you need some way of monitoring race conditions (is there 1 copy of the app running at a time?)

If this is some sort of load process that is running single threaded, then you could select from the table on what needs to be unique. If no results are returned from the select then you run an insert statement:

insert into table 1 (column list) values (initial values)

If a result is returned from the select statement, then you need to run an update statement:

update table_1 
set times_used = times_used + 1
where [whatever makes it unique]

You could do this in a SQL Query with an IF EXISTS (Select statement).

The problem comes when you're running this from multiple sources. You'll need some sort of lock so that if 2 processes check if the record exists at the same time, they don't both try to insert the record.

Answer 3

Based on the information available at this time:

First, it looks like you're using id_no as a surrogate key for Cus_no, Ord_no, Line_seq_no, and allocated_dt. In other words, there will only be one row in Table_1 for any specified set of values in those four columns.

To enforce the uniqueness of those 4 columns, I'd recommend creating a unique index on them. This will guarantee that you can't have two rows with the same values in those four columns.

That said, the primary key on Table_1 will be id_no, and that should be a auto-increment columns (handled in SQL Server by making it an IDENTITY column.

Since whenever a new row is added to Table_1 the value of times_used should be 1, we can set a DEFAULT constraint for that column.

So, the CREATE TABLE statement for Table_1 will look something like this:

CREATE TABLE Table_1
      (id_no  int IDENTITY(1,1) NOT NULL,
       Cus_no int,
       Ord_no int,
       Line_seq_no int,
       allocated_dt DATE,
       times_used int CONSTRAINT DF_Table_1_times_used DEFAULT (1),
       CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (id_no),
       CONSTRAINT UX_Table_1__Cus_Ord_LineSeq_AllocDt
                  UNIQUE NONCLUSTERED (Cus_no, Ord_no, Line_seq_no, allocated_dt)
      );

That lays the groundwork. You may need to adjust to make sure the data types are all correct. Also, if any of the four fields (other than id_no and times_used) would not be part of the check, then you'd need to adjust the unique index.

---

Now, for the actual INSERT or UPDATE:

This looks like a perfect place to use the MERGE statement (a sentence I never expected to utter).

So, if there's already a row with the Cus_no, Ord_no, Line_seq_no, and allocated_dt, you just want to update that row by adding 1 to the times_used value. If there isn't, you want to insert the data, setting the id_no to the next IDENTITY value for the table, and with times_used set to 1.

Our table definition takes care of the id_no and times_used on INSERT, so we can ignore that part.

We'll assume that the Ord_no to find in Table_2 and Table_3 will be in a variable, @Ord_no.

The MERGE statement would look like this:

MERGE INTO Table_1 as t1
USING (SELECT t2.Cus_no, t3.Ord_no, t3.Line_seq_no, t3.allocated_dt
         FROM Table_2 as t2 
                INNER JOIN Table_3 as t3 ON t2.Ord_No = t3.Ord_No
        WHERE t2.Ord_no = @Ord_no) as source
      ON (t1.Cus_no = source.Cus_no AND
          t1.Ord_no = source.Ord_no AND
          t1.Line_seq_no = source.Line_seq_no AND
          t1.allocated_dt = source.allocated_dt)
 WHEN MATCHED THEN 
      UPDATE SET times_used = t1.times_used + 1
 WHEN NOT MATCHED THEN
      INSERT (Cus_no, Ord_no, Line_seq_no, allocated_dt)
      VALUES (source.Cus_no, source.Ord_no, source.Line_seq_no, source.allocated_dt)
;

Basically, what this means - is exactly what we said in the paragraph above.

You can see this working (and generating the results you expect) at this dbfiddle.uk link. Note that I did add values for Line_seq_no. Also, that this is running in SQL Server 2012 - that's the oldest version I could find online right now. However, the MERGE statement is available in SQL Server 2008, so you should be OK.

Also note: I'm taking what you said at face value, as far as doing this for work. If this is for a class, if I were you, I wouldn't use MERGE, unless they've been talking about it in class. You can do the same thing by breaking things down into separate steps: check if the row already exists in Table_1, UPDATE if it does, INSERT if it doesn't.