Login or Register to Ask a Question and Join Our Community


Programming

UPDATE statement: calculating changes

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming UPDATE statement: calculating changes
# 1  
Old 09-14-2009
UPDATE statement: calculating changes
The problem I was working on is solved, but felt it would be worthwhile to ask for some opinions as to whether the approach can actually be improved.
I am using the following example and data taken from SQL datetime calculations and MySQL 5.1:

Code:
CREATE TABLE `data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `temperature` float NOT NULL,
  `change` float NOT NULL,
  PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `data` (`datetime`, `temperature`) VALUES ('2009-01-01 06:07:08','70.4')
,('2009-01-01 06:08:08','62.1')
,('2009-01-01 06:09:08','78.8')
,('2009-01-01 07:08:09','72.6')
,('2009-01-01 07:08:10','63.2')
,('2009-01-01 07:08:11','79.9')
,('2009-01-01 08:09:10','69.3')
,('2009-01-01 11:12:13','64.3')
;

Now, to calculate the changes in temperature, the following query can be used:
Code:
UPDATE data AS D1, data AS D2 SET D1.change = D1.temperature - D2.temperature WHERE D1.id = D2.id + 1;

This works as designed, but requires the need for the data to be sorted first by datetime and - for performance reasons - the addition of an auto-increment called id. Perhaps there are other approaches, which are used commonly in situations where changes in data need to be calculated?
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to apply the update statement in multiple servers on multiple dbs at a time .?

Hi , Can any please help the below requirement on all multiple servers and multiple dbs. update configuration set value='yes' ;1) the above statement apply on 31 Databases at a time on different Ip address eg : 10.104.1.12 (unix ip address ) the above ip box contains 4 db's eg : db... (2 Replies)
Discussion started by: venkat918
2 Replies

2. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

3. Shell Programming and Scripting

Update Statement User menu input screen

Hi Guys, Any good reference for me to perform user database update statement on table which has quite number of fields could be updated depend on user specified column name and the value to assign. All the approaches are welcome and appreciated. Thanks. (1 Reply)
Discussion started by: ckwan123
1 Replies

4. Programming

MYSQL - trigger update on record insert or update

Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works: UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ); So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies

5. Shell Programming and Scripting

Calculating the epoch time from standard time using awk and calculating the duration

Hi All, I have the following time stamp data in 2 columns Date TimeStamp(also with milliseconds) 05/23/2012 08:30:11.250 05/23/2012 08:30:15.500 05/23/2012 08:31.15.500 . . etc From this data I need the following output. 0.00( row1-row1 in seconds) 04.25( row2-row1 in... (5 Replies)
Discussion started by: ks_reddy
5 Replies

6. Shell Programming and Scripting

how to pass a variable to an update sql statement inside a loop

hi all, i am experiencing an error which i think an incorrect syntax for the where clause passing a variable was given. under is my code. sqlplus -s ${USERNAME}/${PASSWORD}@${SID} << END1 >> $LOGFILE whenever sqlerror exit set serveroutput on size 1000000 declare l_rc ... (0 Replies)
Discussion started by: ryukishin_17
0 Replies

7. UNIX for Advanced & Expert Users

Bash executing Orcale Update statement

Hi All, Using Solaris box bash-3.00$ echo $BASH_VERSION 3.00.16(1)-release I have a real bummer of a bug, basically Im running a bash script that executes a bash function "dbase_sql". The bash function accepts a parameter in the form of an Oracle update statement eg ... (1 Reply)
Discussion started by: satnamx
1 Replies

8. Shell Programming and Scripting

How is use sselect statement o/p in insert statement.

Hi All, I am using Unix ksh script. I need to insert values to a table using the o/p from a slelect statement. Can anybody Help! My script looks like tihs. ---`sqlplus -s username/password@SID << EOF set heading off set feedback off set pages 0 insert into ${TB_NAME}_D... (2 Replies)
Discussion started by: nkosaraju
2 Replies

9. Shell Programming and Scripting

If statement - How to write a null statement

In my ksh script, if the conditions of a if statement are true, then do nothing; otherwise, execute some commands. How do I write the "do nothing" statement in the following example? Example: if (( "$x"="1" && "$y"="a" && "$z"="happy" )) then do nothing else command command fi... (3 Replies)
Discussion started by: april
3 Replies
Login or Register to Ask a Question