x

Update entire column after update or insert

There is a table with two columns.

Table:
NewID Int
TrueFalse bit

The bit column is either a 1 or 0. I want to update the bit column for every row anytime a new row is added or updated. So if a new row is inserted that is a '0' then every row will update with a '0' in the TrueFalse column. If a '1' is updated in the TrueFalse column, then every row will update to a '1' in the TrueFalse column.

My first inclinations have been hackneyed and quite poor (trigger, cursor, cursor inside a trigger, etc.) so any advice or ideas are appreciated.
more ▼

asked Feb 02, 2011 at 07:28 PM in Default

ddono25 1 gravatar image

ddono25 1
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

update the complete records after each insert/update.

 INSERT INTO Table (NewID, TrueFalse) VALUES(@ID,@TrueFalse)

UPDATE Table SET TrueFalse = @TrueFalse

more ▼

answered Feb 02, 2011 at 09:30 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

Whilst that would perform the update, I have to support Hakan's blunt answer - the design if flawed and should be changed.
Feb 02, 2011 at 11:50 PM WilliamD
Sorry for beeing blunt, but I got scared of the design that will eventually blow up in someones face when the amount of data has grown to a certain point where no hardware in the world could save the system.
Feb 03, 2011 at 01:27 AM Håkan Winther
@Hakan - No need to be sorry IMO. Your explanation left no uncertainty for the OP that the design must be changed, hence the +1 on your answer from me. I only wanted Cyborg to know his answer is valid from the T-SQL side, but does not indicate the need to fix the broken design.
Feb 03, 2011 at 01:33 AM WilliamD
sorry william i was concentrated on the T-SQL side because @ddono25 was considering using trigger, Cursor etc.
Feb 03, 2011 at 05:58 AM Cyborg
Thank you for understanding and answering the question asked. All the help is appreciated.
Feb 03, 2011 at 12:56 PM ddono25 1
(comments are locked)
10|1200 characters needed characters left

That is a terrible design! What do you think will happen if you have a million records an are trying to insert another million? The first million record gets updated a million times and all the new record inserted will be updated for each records left!

What business rule are you trying to solve with this design? Give us some more information and we may try to help you solve this in an efficient way.
more ▼

answered Feb 02, 2011 at 11:15 PM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

Very good answer!
Feb 02, 2011 at 11:52 PM Magnus Ahlkvist

It is a backup parameters table with less than 30 rows(each row is a database and its backup drive, parameters, etc.). We have to keep more than two weeks of full backups on disk and will keep one week of fulls on one disk and another week of fulls on another disk depending on the week. I was asked by my boss to write the code that would update the column on an update/insert. I am assuming this is more of an academic/education exercise since I am not as experienced.

I appreciate the warnings and understand (even with my limited experience) that this is poor overall design, but it is what my task is. Thanks.
Feb 03, 2011 at 12:54 PM ddono25 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x991
x133

asked: Feb 02, 2011 at 07:28 PM

Seen: 1961 times

Last Updated: Feb 02, 2011 at 07:28 PM