473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update question

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
Jul 23 '05 #1
16 1402
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
--

Jul 23 '05 #2
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
Jul 23 '05 #3
Somebody have any ieas???

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #4
What are you trying to achieve that you can't already do with a set of
IF statements?
My logic is that when some event occured (param1) then i need to update some field.


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
--

Jul 23 '05 #5
>What are you trying to achieve that you can't already do with a set of
IF statements?
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.
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 ...
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 sadThat might seem more concise than a set of IF statements but it
probably won't be as efficient.


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
Jul 23 '05 #6
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.PhoneChanedEvent() THEN @param2 ELSE
col1 END,
...............
.......................
Thanks

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #7
You can generate string with needly update sql and than call EXECUTE(@updateSql).

akej via SQLMonster.com wrote:
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 ??

Jul 23 '05 #8
I'm not accessable to use dynamic SQL, Thanks
ANY IDEAS???

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #9
akej via SQLMonster.com (fo***@SQLMonster.com) writes:
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.
Which may be due to, as David hinted, bad table design.
UPDATE YourTable
SET col1 = CASE WHEN @param1 = 222 THEN @param2 ELSE col1 END,
col2 = CASE WHEN @param1 = 333 THEN @param2 ELSE col2 END,
...
WHERE ...


Is optimizer do according the above statement ???


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.
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)


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10
Thanks Erland.

My question is: Is it good idea to use

UPDATE YourTable
SET col1 = CASE WHEN @param1 = dbo.PhoneChanedEvent() 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
Jul 23 '05 #11
akej via SQLMonster.com (fo***@SQLMonster.com) writes:
My question is: Is it good idea to use

UPDATE YourTable
SET col1 = CASE WHEN @param1 = dbo.PhoneChanedEvent() 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???


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #12
On Fri, 20 May 2005 18:19:19 GMT, akej via SQLMonster.com wrote:
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.PhoneChanedEvent() THEN @param2 ELSE
col1 END,
..............
......................
Thanks


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 @PhoneChanedEvent int -- or whatever datatype it should be
SET @PhoneChanedEvent = dbo.PhoneChanedEvent()
UPDATE YourTable
SET col1 = CASE WHEN @param1 = @PhoneChanedEvent THEN @param2 ELSE col1
END,
...............
.......................
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #13
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
Jul 23 '05 #14
akej via SQLMonster.com (fo***@SQLMonster.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #15
OK thanks, maybe, however what does it mean
turn the column into rows.


--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #16
akej via SQLMonster.com (fo***@SQLMonster.com) writes:
OK thanks, maybe, however what does it mean
turn the column into rows.


Instead of having:

CREATE TABLE xyx (object_id int NOT NULL PRIMARY KEY,
thisproperty bit NOT NULL,
thatproperty bit NOT NULL,
...
You have

CREATE TABLE xyz_properties (object_id int,
property_code varchar(5),
value bit,
CONSTRAINT pk_xyx_prop PRIMARY KEY (object_id, property_code),
CONSTRAINT fk_xyz_objid FORIEGN KEY (object_id)
REFERENCES xyz(object_id),
CONSTRAINT fk_xyz_property FORIEGN KEY (property_code)
REFERENCES property_codes(property_code))

This table property_codes would be one you loaded once for all when
you install the system.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st connection SELECT * FROM nextkey WHERE tblname = 'PLCN'...
0
by: chan | last post by:
how to apply online update function into program (the effect just like Norton system work live update) The situation is below: I want to develop a program that contains some product...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
2
by: Miro | last post by:
I will ask the question first then fumble thru trying to explain myself so i dont waste too much of your time. Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an index - i...
12
by: si_owen | last post by:
Hi all, I have a SQL query that worked fine in my project until it came to testing. I found that the NvarChar fields I have wont accept the use of an ' My code and query is here does anyone...
13
by: shookim | last post by:
I don't care how one suggests I do it, but I've been searching for days on how to implement this concept. I'm trying to use some kind of grid control (doesn't have to be a grid control, whatever...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.