Complex UPDATE problem.

[haqyunus]

Helo,
I have a complex UPDATE problem.
Suppose i have a table 'tblA' and there is a column 'ColA' in it. Now when i update a single cell of 'ColA' of a certain row of 'tblA' with 'newvalue', i also want to update (at the same time) the rest of the column (of rest of the rows) with 'newvalue/2'. How can i do this?

what i mean is:
UPDATE tblA
SET ColA = newvalue

--This will update the rows of 'tblA' one by one row,now i want to update the rest of the column too before moving on to the next row. Sort of a recursive problem?

Please help. I will be very obliged.
Thanks Haq.

 

[ClaireHCM]

You can use case to do the update
Here is an example:

create table c (id int,name varchar(10))
go

insert into c select 1,hi
insert into c select 1,hi
insert into c select 2,hi
insert into c select 3,hi
go
update c set name =(case when id = 1 then 'hello' else 'hi' end)
go

 

[billchris]

Hi Haq.

I'm not quite sure what you are trying to do. It appears that you want to update colA in every row to a single 'newvalue' and colB, colC, coD... to 'newvalue/2'.

If that's the case then:

  UPDATE 
    tblA
  SET
    colA = newvalue,
    colB = newvalue/2,
    colC = newvalue/2,
    colD = newvalue/2,
    ...

would work.

But without some more information, that doesn't make any sense because then every record would be identical and I can't think of a reason to have a table full of identical records.

I think my confusion is arising from this sentence, "Now when i update a single cell of 'ColA' of a certain row of 'tblA' with 'newvalue', i also want to update (at the same time) the rest of the column (of rest of the rows) with 'newvalue/2'."

When you say "single cell", do you mean column? And, when you say "the rest of the column (of rest of the rows)", do you mean colA in every other row, or colB, colC, etc in the current row and then repeat with a different 'newvalue' for every other row?

Please expand on your question a little.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[haqyunus]

thanks for ur reply.

I mean to say :
"the rest of the column ColA for rest of the rows".

as on the other hand u r right as there is no use of upadting rest of the table with identical values.

i want to do something like this:

UPDATE
tblA
SET
colA = newvalue,
set rest of the colA = newvalue/2,

 

[JamesLean]

As per ClaireHsu's answer:

UPDATE tblA
SET colA = CASE WHEN id = 123 THEN 10 ELSE 5 END

(123 is the row you want to update).

--James

 

[haqyunus]

thanks JamesLean...

but will it work if i want to do this (which is my actual question).

"Let I = 0
i update single row with id I, after the updating the row I, i want to up date the rest of the same column with some value.

Now i came to the second row i.e. I++. This row should have be been updated once (as a side effect) when we were updating row I. Now when I++ is updated then row I should also be updated, this time as a side effect of updation of row I++....

and so on till we reach end of the table."

can you tell me how can i do that?

 

[ClaireHCM]

For the first question still you can use case to accomplish it.According to your requirment.

can you give some sample sata?
And laso the desired result.

 

[billchris]

This will do what you want. Just change the values of the @RecordId and @NewValue variables. If you want to make it even easier, create a stored procedure.

  DECLARE @RecordId int,
  DECLARE @NewValue decimal(9,4)  

  SET @RecordId = 123
  SET @NewValue = 15

  UPDATE 
    tblA
  SET 
    colA = 
      CASE 
        WHEN id = @RecordId 
        THEN @NewValue 
        ELSE @NewValue/2 
      END
GO

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[haqyunus]

Thanks billchris

but the coe u have given me will work for only row with id: @RecordId.

U mean to apply this to the whole table i have to use a loop that will iterate through the rows by incrementing @RecordId? But dont u think this will be too inefficient for 10000 rows?


--------
Thanks ClaireHsu

suppose this is the table

RowID ColA
------------
1 2
2 4
3 8
4 6

now i want to do is that i update the whole table such:
that

when RowID = 1 set colA = newVal and at the same time rest if the ColA to newVal/2. Now move on to the next row i.e. RowID = 2. Update ColA=newVal2 and rest of the ColA to newVal2/2. This time value in RowID=1 will also be updated as a side effect of updating RowID=2....and so on...

i again state my query by rephrasing it:
Let I = 0
i update single row with id I, after the updating the row I, i want to up date the rest of the same column with some value.

Now i came to the second row i.e. I+1. This row should have be been updated once (as a side effect) when we were updating row I. Now when I+1 is updated then row I should also be updated, this time as a side effect of updation of row I+1....

and so on till we reach end of the table i.e. row I+n, where n is the number of rows in the table."

 

[meydz]

i think this will work!

suppose you have table A, this contains the value to be updated
ID_no Value
1 2
2 4
3 8
4 6
and table B that contains your new value
ID_no New_Value
1 4
2 6
3 8
4 10

the result would be
1 5
2 5
3 5
4 10
right?

if you are expecting this result then try this SQL

update A
set value = case A.id_no when B.id_no then B.new_value else B.new_value /2 end
from
A
cross join B


i hope this will help!!!


meydz