Today I had to change values in our database at work. We have Sets here that contain Instruments, each set has a STE value (number of sets that can be put in a washing machine). This was never used and now all of a sudden they needed every net to have such a number, so it was up to me to update 5000+ sets.

The definition of an STE was pretty simple, each set has a basket included in it’s instrument list on which the STE value can be based, but how can I get SQL to fill in the STE based on an instrument. This means using 2 different tables!

Using the code below I was able to select all sets that have an STE of 500 (with descriptions and everything), this all was based on an earlier topic I had done here, Select data from multiple tables and join them in MS SQL 2005.

Code:

 SELECT    PSET.Bezeichnung, PSET.Sterileinheiten
FROM        PSET INNER JOIN
                      PSETPOS ON PSET.SNummer = PSETPOS.SNummer
WHERE    (PSETPOS.INummer = '235724')

Let’s start simple with a short introduction to the UPDATE command.

The UPDATE statement is used when a table already contains data which needs to be altered. Here is the very basis of the statement.

Code:

 UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}

So if I would want to change the PSET.Sterileinheiten I would need to set the table_name to PSET (table where all the sets are located), column_1 to Sterileinheiten (the STE value used by the program) and the {condition} to PSETPOS.INummer = ‘235724’ (number of the Instrument that should have 500 as STE value). Then at [new value] I would need to change the value that I want to use, in this case that would be 500. Sounds pretty easy now doesn’t it?

Yeah, and that is the problem, because I use both the PSET and PSETPOS (table where all the instruments are linked to the sets) tables MS SQL doesn’t understand what I did. I need to JOIN them in some way to let SQL know what value is used to match both tables.

Code:

 UPDATE PSET
SET PSET.Sterileinheiten = '500'
WHERE    (PSETPOS.INummer = '235724')


So how can we make the above code work for us. To do this we need to add an INNER JOIN statement, just like we had in the select query. And when we know that, then it is THAT easy. We just put the FROM statement underneath the SET and the job is done.

Code:

 UPDATE PSET
SET PSET.Sterileinheiten = '500'
FROM        PSET INNER JOIN
                      PSETPOS ON PSET.SNummer = PSETPOS.SNummer
WHERE    (PSETPOS.INummer = '235724')


Of course if you have any questions on the topic above, let me know. I hope to make things as clear as possible for everyone.