Changing the case of letters in SQL - update of rows

Question

Changing the case of letters in SQL - update of rows

j.clay 47557

SSC Eights!

Points: 835
More actions
September 18, 2018 at 8:20 am

#372805

Hi there
I have a large table showing lists of world countries with some rows showing e.g. UNITED KINGDOM and some showing United Kingdom. I want to make all the countries appear in upper case letters, so I'm using the following SQL:
UPDATE contact
SET country=upper(country)
WHERE serialnumber='100045'
This works fine, however I'm slightly concerned about something. Whenever I run the script I get a message saying that 4 rows have been affected by the query. Each serialnumber only appears once in the contact table, so I can't work out why 4 rows would be updated rather than just 1.
Does anyone know why this might be the case? Alternatively, is there a way in which I can see exactly which rows have been updated in the database?
Many thanks
Jon

Viewing 11 posts - 1 through 10 (of 10 total)

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Have you performed a count and that same WHERE clause in a query?

That will tell you exactly how many rows have that ID. Or is there a unique constraint on that column? That will allow only one ID of that particular number.

Andrew SQLDBA

mark.humphreys SSC Eights! Points: 867 More actions · Answer 2

Hi Jon,

You should be able to run a SELECT statement using that WHERE clause, and it should show you what you are about to update.

SELECT country

FROM contact

WHERE serialnumber = '100045';

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 3

Hi Andrew

Thanks for your help.

Running the following SQL shows just 1 record.

SELECT COUNT(serialnumber)
FROM Contact
WHERE Serialnumber='100045'

The column is a Primary Key although it doesn't allow NULLS which (I think!) makes it have a unique key constraint.

Best wishes
Jon

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 4

Hi Mark

Thanks for your help.

Yes, running that query only produces one line (as it should do). The mystery deepens!

Best wishes
Jon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Any triggers on the table that are fired by an update statement?

mark.humphreys SSC Eights! Points: 867 More actions · Answer 6

Hi Jon,
Just re-reading you OP you say you have a large table containing a list of countries with some being UNITED KINGDOM and some United Kingdom. Do they all have the same serial number or different ones.

Just trying to understand how rows you expect to update?
Thanks
Mark

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 7

Hi Mark

Each person in the contact table (and therefore each entry in that table) is unique, so it might be something like this:

1 UNITED KINGDOM
2 United Kingdom
3 FRANCE
4 France
5 UNITED KINGDOM
6 France

So, each serialnumber only has 1 country attached to it. In my original script I expected only 1 line to get updated because serialnumber '100045' only has 1 country attached to it.

Best wishes
Jon

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 8

Lynn Pettis - Tuesday, September 18, 2018 9:10 AM
Any triggers on the table that are fired by an update statement?

Hi Lynn

Thank for your help. I think you might be onto something here!

I've just looked at the Triggers section of the table and I can see 8 triggers. I'll check them out and see what they do.

Best wishes
Jon

mark.humphreys SSC Eights! Points: 867 More actions · Answer 9

Hi Jon,

If this doesn't exist you could use this example to build triggers to handle auditing then you would see what is being updated.
https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/.
However check out the triggers that you have discovered as Lynn may have nailed it for you.
Thanks
Mark

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 10

Thank you everyone. It turns out that Lynn's answer r.e. the triggers was the one 🙂

Best wishes
Jon