Hi, Pls help me about this Update Query

[netbytes]

Hi

I want to add the three fields then have the answer in the fourth field.

Heres an example:

Table: A

Fields in table A

field1
field2
field3
answer

before updating:

field1-----field2-----field3-----answer
2---------0---------1---------

after updating:

field1-----field2-----field3------answer
2---------0---------1----------3


so how to write this using an update query?

i have my own code but its not working because i think its wrong.

UPDATE A SET A.answer = SUM(A.field1,A.field2,A.field3);

Pls help me about this.


Thank you.

 

[WayPay]

UPDATE A SET A.answer = (A.field1 + A.field2 + A.field3);

SUM works on values in multiple records in a specific column.

 

[boblarson]

Storing calculated data that way is not a recommended way to work with normalized data. You should be getting the sum at runtime via a query instead, for 99% of data situations. There are some rare situations where you would store that data but that is the exception rather than the rule.

What type of data are you trying to store?

 

[netbytes]

Storing calculated data that way is not a recommended way to work with normalized data. You should be getting the sum at runtime via a query instead, for 99% of data situations. There are some rare situations where you would store that data but that is the exception rather than the rule.

What type of data are you trying to store?

actually im trying to store Strings and Null Values.

How to write that? thank you.

 

[Rabbie]

actually im trying to store Strings and Null Values.

How to write that? thank you.

Are you being sarcastic??. You code suggests you are trying to write the results of a calculation which is not normally a sensible thing to store in a table. Much better to calculate it when you need it in a form/report/query.

 

[netbytes]

oops sorry sir, im not..
Im from Philippines.. i think you misunderstood my writings .

sorry for the wrong construction of my sentence.. if its sarcastic to you sir.

anyway i just want to ask for the write syntax. im trying hard to construct my code but. still i cdnt get the right one..

thx

 

[netbytes]

UPDATE A SET A.answer = (A.field1 + A.field2 + A.field3);

SUM works on values in multiple records in a specific column.

Thank you Waypay.
It's working

1 reputation for you.

 

[Rabbie]

oops sorry sir, im not..
Im from Philippines.. i think you misunderstood my writings .

sorry for the wrong construction of my sentence.. if its sarcastic to you sir.

anyway i just want to ask for the write syntax. im trying hard to construct my code but. still i cdnt get the right one..

thx

Apology accepted. Your talk of NULLs has me a little confused because if your example is accurate then there shouldn't be any nulls.

If you can have null values in field1,field2,field3 then you may need to use the NZ function to set any null values to 0 so try

UPDATE A SET A.answer = (NZ(A.field1,0) + Nz(A.field2,0) + NZ(A.field3,0));

Replace A in this with the name of your table

 

[netbytes]

Apology accepted. Your talk of NULLs has me a little confused because if your example is accurate then there shouldn't be any nulls.

If you can have null values in field1,field2,field3 then you may need to use the NZ function to set any null values to 0 so try

UPDATE A SET A.answer = (NZ(A.field1,0) + Nz(A.field2,0) + NZ(A.field3,0));

Replace A in this with the name of your table

Thank you sir,
Its working..

1 reputation for u