• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

MySQL procedure or function

Hi,

I would like to copy the records from table 'A' to table 'B'.
Table A:
timestamp | value | varname | file_h | file_o
Table B:
timestamp | blockid | value | varname | exported | file_h

Notice there are field to compute: blockid and exported

to compute field exported:
if the file_h is NULL, set exported to -1, else set it to 1

to compute the blockid here is some code:

DECLARE mytimestamp DATETIME;
SET mytimestamp = ADDTIME(    <<the timestamp from the table>>  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;

1. What do I need ? procedure, function, ... ?
2. Can you compelte some code.

Thank you for any help.
0
jamesbond007_
Asked:
jamesbond007_
  • 4
2 Solutions
 
Tomas Helgi JohannssonCommented:
   Hi!

Easiest would be to create an function with an timestamp as an input variable
and varchar as an output variable and

http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html



Regards,
   Tomas Helgi
CREATE FUNCTION THEBLOCKID (ts TIMESTAMP) RETURNS VARCHAR(10) 
BEGIN
DECLARE mytimestamp DATETIME;
DECLARE blockid = VARCHAR(10);
SET mytimestamp = ADDTIME(    <<the timestamp from the table>>  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;
return BLOCKID;
END;
    

Open in new window

0
 
Tomas Helgi JohannssonCommented:
Sorry small error in code.

Regards,
   Tomas Helgi
CREATE FUNCTION THEBLOCKID (ts TIMESTAMP) RETURNS VARCHAR(10) 
BEGIN
DECLARE mytimestamp DATETIME;
DECLARE blockid = VARCHAR(10);
SET mytimestamp = ADDTIME(    ts  , '0 00:15:00');
IF m = 0 THEN
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), '00')
ELSE
blockid = CONCAT(SUBSTRING(mytimestamp,1,4),
SUBSTRING(mytimestamp,6,2), SUBSTRING(mytimestamp,9,2), SUBSTRING(mytimestamp,12,2), m)
END IF;
return BLOCKID;
END;

Open in new window

0
 
jamesbond007_Author Commented:
Hi,

I do not need the blockid, I need a to copy records from a table to another.
0
 
Tomas Helgi JohannssonCommented:
Then simply use

insert into table a(column,...) select (columns,...) from table b

and you could call the function in the select statement.

Regards,
    Tomas Helgi
0
 
Tomas Helgi JohannssonCommented:
sorry
insert into table b(column,...) select (columns,...) from table a  :)

Regards,
   Tomas Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now