Update query

Question

Post reply

Update query

joe joe-152023

SSCommitted

Points: 1819
More actions
April 18, 2005 at 3:54 am

#88689

Hi there,
I want to update column B. But I have several problems.
It goes like this,
I have a tableX with a Column A - numbers from 001 to 999 and letters A to Z (char 4) , and a Column B which is blank right now (char 1). I want to update Column B with an E when the Value in Column A is an Equal number, and an U when its an Unequal number.
I have tried with
UPDATE TableX
SET ColumnB = 'E'
SELECT Cast(ColumnA as int)
From TableX
Where 2 * ((Cast(ColumnA as int))/2) = Cast(ColumnA as int)
But this update ColumnB with E all over, I need to put in an "Else" or perhaps a smarter SQL sentence.
Any ideas or Hits?
Thanks Joejoe

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

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

UPDATE TableX

SET ColumnB = SUBSTRING('EU',(ColumnA % 2)+1,1)

Far away is close at hand in the images of elsewhere.
Anon.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Wow, that with substring looks nice :-). You even managed to get something about European Union into it... is this following some new Euro-SQL directives from Brussels?

However, I think that if joe simply deletes the third line of his code, he will probably get the same result. Problem is that the SQL as it is written in fact means TWO actions:

--------- part 1 ------

UPDATE TableX

SET ColumnB = 'E' (no restrictions = all rows updated)

---------- part 2 - select only, no update-----

SELECT Cast(ColumnA as int)

From TableX

Where 2 * ((Cast(ColumnA as int))/2) = Cast(ColumnA as int)

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 3

Hi there,

Thanks for helping me.

I just througt that I had solved the problem with this one,

UPDATE TableX

SET ColumnB = CASE WHEN NOT EXISTS

(SELECT Cast(ColumnA as int) FROM TableX WHERE 2 * ((Cast(ColumnA as int))/2) = Cast(ColumnA as int))

THEN 'E' ELSE 'U' END;

But this puts U all over in ColumnB.

What I think I need is an Conditional Update.

Regrads Joejoe

Vladan SSC-Insane Points: 22072 More actions · Answer 4

joe,

please don't be confused by my joke at EU, what I said about David's code being nice was meant in earnest. It is the best solution, because it does the conditional update to E or U as necessary, in one run. I just added my explanation to show where your code went wrong.

Try David's code, it should work precisely as you need.

Vladan

EDIT: Your second code does not work, because the NOT EXISTS evaluates all rows from the table. It would work only if you have some ID in every row, so that you can limit the EXISTS to one row, like :

CASE WHEN NOT EXISTS

(SELECT Cast(ColumnA as int) FROM TableX xx WHERE 2 * ((Cast(xx.ColumnA as int))/2) = Cast(xx.ColumnA as int) AND xx.id = TableX.id

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 5

I'm working on it.

Could one of you guys perhaps elaborate the explanation to David's code a littel bit?

Regards Joejoe

Vladan SSC-Insane Points: 22072 More actions · Answer 6

What the code does is, that it calculates remainder of division of the number by 2. If remainder = 0, then it takes the first character. If remainder is 1, it takes the second character from the 'EU' string (that's why you have to add 1, to make 1 from 0 - and 2 from 1). It may look a little confusing at first (I also wondered why to add 1,1... but it is not a decimal number. The first "1" is to be added, the second "1" is another parameter - how many charaters do you want to return... see SUBSTRING in BOL).

Also from BOL:

% (Modulo)

Provides the remainder of one number divided by another.

Syntax

dividend % divisor

Arguments

dividend

Is the numeric expression to divide. dividend must be any valid Microsoft® SQL Server™ expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

Result Types

int

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 7

Thanks for the explanation Vladan!

When I run David's code it says;

Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the varchar value '160A' to a column of data type int.

The ColumnA is Char, when I try with

UPDATE TableX

SET ColumnB = SUBSTRING('EU', Cast(ColumnA as Int) % 2)+1,1)

It come up with the same error. Do you have any suggestions?

Thanks again.

noeld SSC Guru Points: 96590 More actions · Answer 8

UPDATE TableX

SET ColumnB = SUBSTRING('EU', Cast(Left(ColumnA,3) as Int) % 2)+1,1)

* Noel

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Right, I overlooked in your original post, that the column contains both numbers AND letters. If it is always so that the first 3 characters are numbers and the fourth is alphabetical sign, then noeld's modification is what you need. If it is more complicated - like if there are certain exceptions (14A, 8789 etc.) - then you'll have to do it some other way. However, as I read your first post, this is not the case... unless there are some typing errors or other entries that you overlooked.

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 10

Thanks noeld!

Vladan you are right, the 3 first characters are allways numbers.

But when I run the code noeld posted it comes up with this,

Server: Msg 174, Level 15, State 1, Line 2

The substring function requires 3 arguments.

I'm lost I can't figure it out.

Help.

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 11

I was missing a parenthese. This is how is should look like,

UPDATE TableX

SET ColumnB = SUBSTRING('EU', (Cast(Left(ColumnA, 3) as Int) % 2)+1,1)

Thanks alot for the help (All of you)

Regards Joejoe