Update question

[akej via SQLMonster.com]

Suppose i have table with 20 columns.

My store proc takes two input parameters (param1, param2), now i need to
UPDATE column with param2 according to param1:
e.g


if param1 = 222 --> UPDATE col1
if param1 = 333 --> UPDATE col2
............... ...
............... ...
............... .............

Is it possible to acomplish it wiithout write 20 times the IF and UPDATE ??

--
Message posted via http://www.sqlmonster.com

[David Portas]

Re: Update question

You could certainly use a CASE statement to do this:

UPDATE YourTable
SET col1 = CASE WHEN @param1 = 222 THEN @param2 ELSE col1 END,
col2 = CASE WHEN @param1 = 333 THEN @param2 ELSE col2 END,
...
WHERE ...

That might seem more concise than a set of IF statements but it
probably won't be as efficient.

What exactly is the logic you are trying to represent? I can't think of
many good reasons for wanting to pass the parameters that way. Seems
like it may indicate a design flaw if you need to reference many
columns by some sort of index.

--
David Portas
SQL Server MVP
--

[akej via SQLMonster.com]

Re: Update question

Thank u David,
However i used technique that u advice me but it's not efficient, BTW is
optimizer don't UPDATE the field with itself??

My logic is that when some event occured (param1) then i need to update
some field.

Any ideas??

Thanks

--
Message posted via http://www.sqlmonster.com

[akej via SQLMonster.com]

Re: Update question

Somebody have any ieas???

--
Message posted via http://www.sqlmonster.com

[David Portas]

Re: Update question

What are you trying to achieve that you can't already do with a set of
IF statements?
[color=blue]
> My logic is that when some event occured (param1) then i need to[/color]
update[color=blue]
> some field.[/color]

I understand that's what you want to do. I don't understand WHY you
would want to do it. Each column is supposed to represent a different
attribute. I find it difficult to think of a scenario where what you
have said would make much sense - except for the obvious case where you
have a poor table design with a repeating group of columns that you
reference by an index value (param1). In that case the best solution
would be to redesign the table.

Another possibility is that you want to parameterize column names as an
additional level of abstraction to promote code re-use in your SPs.
That's not generally a good strategy - at least not, I think, one that
most people would recognize as best practice. Columns are identified by
column name. For reasons of performance, reliability and ease of
maintenance the practice commonly adopted is to create a separate SP
for each distinct type of update - not necessarily for each unique set
of columns that will be updated but at least with a fixed set of input
parameters that correlate with a fixed set of columns. This is
particularly important for parameters used in the WHERE clause of an
UPDATE statement. It ensures that SQL Server can choose and re-use the
best execution plan for the proocedure.

If you still want to consider other possibilities you may want to look
at Dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html

If you aren't already familiar with dynamic SQL then make sure you read
that whole article and understand the full implications.

Hope this helps.

--
David Portas
SQL Server MVP
--

[akej via SQLMonster.com]

Re: Update question

>What are you trying to achieve that you can't already do with a set of[color=blue]
>IF statements?[/color]

I can do it with IF statements however in case i will have more then 20
columns i wiil need to write a lot of time these statements it's looks like
bad programming.
[color=blue]
>You could certainly use a CASE statement to do this:[/color]
[color=blue]
>UPDATE YourTable
>SET col1 = CASE WHEN @param1 = 222 THEN @param2 ELSE col1 END,
>col2 = CASE WHEN @param1 = 333 THEN @param2 ELSE col2 END,
>...
>WHERE ...[/color]

Is optimizer do according the above statement ???


For now i have two options for updating
1. IF's statement.
2. with cases.


The second option is preferable for me.however as you sad[color=blue]
>That might seem more concise than a set of IF statements but it
>probably won't be as efficient.[/color]

I don't want to use the dynamic SQL also i'm not familiar with it.

IS it third option to solve my problem (it's not a problem obviously)

What u can advice me

--
Message posted via http://www.sqlmonster.com

[akej via SQLMonster.com]

Re: Update question

I want be more precise in my explanation.

First of all the table that i need to UPDATE consist details of USER,
all that i need to it's to update one of it's detail.

When an event occured the client call to store proc and pass two
parameters:

param1 = event id
param2 = event value

in my db i have function for each event (the all functions just return the
event id)

e.g.

UPDATE YourTable
SET col1 = CASE WHEN @param1 = dbo.PhoneChaned Event() THEN @param2 ELSE
col1 END,
...............
............... ........


Thanks

--
Message posted via http://www.sqlmonster.com

[Konstantin Kosinsky]

Re: Update question

You can generate string with needly update sql and than call EXECUTE(@update Sql).

akej via SQLMonster.com wrote:[color=blue]
> Suppose i have table with 20 columns.
>
> My store proc takes two input parameters (param1, param2), now i need to
> UPDATE column with param2 according to param1:
> e.g
>
>
> if param1 = 222 --> UPDATE col1
> if param1 = 333 --> UPDATE col2
> ............... ..
> ............... ..
> ............... ............
>
> Is it possible to acomplish it wiithout write 20 times the IF and UPDATE ??
>[/color]

[akej via SQLMonster.com]

Re: Update question

I'm not accessable to use dynamic SQL, Thanks


ANY IDEAS???

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: Update question

akej via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> I can do it with IF statements however in case i will have more then 20
> columns i wiil need to write a lot of time these statements it's looks
> like bad programming.[/color]

Which may be due to, as David hinted, bad table design.
[color=blue][color=green]
>>UPDATE YourTable
>>SET col1 = CASE WHEN @param1 = 222 THEN @param2 ELSE col1 END,
>>col2 = CASE WHEN @param1 = 333 THEN @param2 ELSE col2 END,
>>...
>>WHERE ...[/color]
>
> Is optimizer do according the above statement ???[/color]

I don't think the overhead for updating a lot of columns with their
current value incurs much overhead. Possibly constraints are checked
once extra.

However, if there is a trigger on the table with IF UPDATE(col) clauses,
using CASE will have an impact, becausr UPDATE(col) only reflects
whether the column appeared in the SET statement, not whether there
was any actual change.
[color=blue]
> For now i have two options for updating
> 1. IF's statement.
> 2. with cases.
>...
> IS it third option to solve my problem (it's not a problem obviously)[/color]

Yes, there is. Redesign the table so that what now is columns becomes
rows instead. Since I don't know what is in that table, I have no idea
whether this would be a good idea, but at least your update statement
would be very straightforward .


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[akej via SQLMonster.com]

Re: Update question

Thanks Erland.

My question is: Is it good idea to use

UPDATE YourTable
SET col1 = CASE WHEN @param1 = dbo.PhoneChaned Event() THEN @param2 ELSE
col1 END,
............... .......
............... .......
............... .......

In such UPDATE only ONE column will updates in one time, however it seems
like if the CASE statement is false then i perform SET col1 = col1
it's not efficient as DAVID hinted, but maybe the optimaizer not UPDATE
column with itself???

THANKS

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: Update question

akej via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> My question is: Is it good idea to use
>
> UPDATE YourTable
> SET col1 = CASE WHEN @param1 = dbo.PhoneChaned Event() THEN @param2 ELSE
> col1 END,
> ............... ......
> ............... ......
> ............... ......
>
> In such UPDATE only ONE column will updates in one time, however it seems
> like if the CASE statement is false then i perform SET col1 = col1
> it's not efficient as DAVID hinted, but maybe the optimaizer not UPDATE
> column with itself???[/color]

And I tried to answer that question in my last post. There is no extra
cost for updating extra columns on a row; the big cost is finding the
row. What could be an issue is triggers, as I mentioned in my post.
And, I will have to admit, I don't know how the transaction log works
in this case. I can see possibilities:
1) SQL Server always stores the entire row to the t-log
2) SQL Server only writes the updated rows into the t-log.
3) As 2, but only actual changes are stored.

Only in 2) updating extra rows would matter. You would have to have a
considerable volume for this to be really significant though.

I was looking in "Inside SQL Server 2000", but I could not find anything
about this, but maybe I was looking in the wrong place.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Update question

On Fri, 20 May 2005 18:19:19 GMT, akej via SQLMonster.com wrote:
[color=blue]
>I want be more precise in my explanation.
>
>First of all the table that i need to UPDATE consist details of USER,
>all that i need to it's to update one of it's detail.
>
>When an event occured the client call to store proc and pass two
>parameters:
>
>param1 = event id
>param2 = event value
>
>in my db i have function for each event (the all functions just return the
>event id)
>
>e.g.
>
>UPDATE YourTable
>SET col1 = CASE WHEN @param1 = dbo.PhoneChaned Event() THEN @param2 ELSE
>col1 END,
>............ ..
>.............. ........
>
>
>Thanks[/color]

Hi akej,

I'm not sure, but I think that this will make SQL Server re-execute the
user-defined function for each row in the table. This makes sense if you
use one or more column names in the parameter list, but is not needed in
your case.

Try if this improves the performance:

DECLARE @PhoneChanedEve nt int -- or whatever datatype it should be
SET @PhoneChanedEve nt = dbo.PhoneChaned Event()
UPDATE YourTable
SET col1 = CASE WHEN @param1 = @PhoneChanedEve nt THEN @param2 ELSE col1
END,
...............
............... ........


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[akej via SQLMonster.com]

Re: Update question

Thanks, so how it can be issued is triggers ?

for now i have job that run every "n" minuts and call to the store
procedure that take parameters as EventID ......

can u help me, please. THANKS.

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: Update question

akej via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> Thanks, so how it can be issued is triggers ?
>
> for now i have job that run every "n" minuts and call to the store
> procedure that take parameters as EventID ......
>
> can u help me, please. THANKS.[/color]

So precisely what is your problem with triggers. I pointed out that there
is a potential issue with triggers if you use the CASE expression, but
since I know nothing about your application, I have no idea if there is a
real issue.

However, the more this thread keeps going, I get a stronger feeling that
you should redesign the table, and turn the column into rows. This makes
it all much simpler.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp