archiving table records to another table by trigger(move daialy table records to weekly table, evry day)


HOME ยป Databases

I have written this trigger in mysql 5:


create trigger changeToWeeklly after insert on tbl_daily for each row
begin
insert into tbl_weeklly SELECT * FROM vehicleslocation v
where v.recivedate < curdate();
delete FROM tbl_daily where recivedate < curdate();
end;


i want to archive records by date, move yesterday inserted record from dailly to weekly table and last weekly table to mounthly table and deletes this records from previous table
this trigger has following error when insert in daily tabled occurred :
"Can't update table 'tbl_daily' in stored func


Related to : archiving table records to another table by trigger(move daialy table records to weekly table, evry day)
Move million records from MEMORY table to MYISAM table
Databases

I am looking for a fast way to move records from a MEMORY table to MYISAM table. MEMORY table has around 0.5 million records. Both tables have exactly the same structure (same number of columns, data types etc.). But the MYISAM table is indexed (B-TREE) on a few columns. There are around 25 columns most of which are unsigned integers.


I have already tried using "INSERT INTO SELECT * FROM " query. But is there any faster way to do this?


Appreciate your help.


Prashant


mysql join show all records in left table with a flag for matching records in right table
Databases

I need a help from mySQL Join tables which should list all users in 'Table A' and also should show flag by matching records in 'Table B'


Table A (users)
=====================
id | name
=====================
1 | aaa
2 | bbb
3 | ccc
4 | ddd
5 | eee
Table B (users_likes)
=====================
like_by | like_to
=====================
1 | 2
1 | 3
2 | 3
4 | 1
5 | 1

if user 'aaa'(id:1) login to the system and performs a search to list all users except his details so the r

MYSQL Update single records a table with MIN & MAX values from multiple records in 2nd table MYSQL
Databases

I have 2 tables houses & availability - I want to find the min and max for the RENT field for each HOUSEID in AVAILABILITY this contains many records per JOUSEID , then UPDATE the HOUSES table fields MIN_RENT and MAX_RENT, the HOUSES table contains only one entry per HOUSE ID


HOUSES

Fields

houseid

min_rent

max_rent


Data

houseid = 121
min_rent = 40.00

max_rent = 90.00


houseid = 122

min_rent = 50.00

max_rent = 80.00


houseid = 123

min_rent = 40.00

nax_rent = 100.00



Combine multiple records in a table into one field in another table based on fields in a third table
Databases

I have three tables:


Table 1:
pid, name, address
1 Dale BLANK
Table 2:
pid, moid
1 12
Table 3:
moid, address
12 123 Main
12 12 South
12 99 North

For output I need:


Table 1:
pid, name, address
1 Dawn 123 Mian,12 South,99 North

How can i combine multiple addresses in table 3 into one address field in table 1 linking the two tables with information in table 2?


archiving table records to another table by trigger(move daialy table records to weekly table, evry day)
Databases

I have written this trigger in mysql 5:


create trigger changeToWeeklly after insert on tbl_daily for each row
begin
insert into tbl_weeklly SELECT * FROM vehicleslocation v
where v.recivedate < curdate();
delete FROM tbl_daily where recivedate < curdate();
end;


i want to archive records by date, move yesterday inserted record from dailly to weekly table and last weekly table to mounthly table and deletes this records from previous table
this trigger has following error when insert in daily tabled occurred :
"Can't update table 'tbl_daily' in stored func

Select records from mysql table one by one - dynamic table with more records continuously being written
Databases

I want to select one record at a time from a MySQL table. I found a similar post on SO here -


How to select records one by one without repeating


However, in my case, the table size is not fixed, The data is continuously been added to the table and I want to select one record at a time from this table. Also, I'm using python to connect to the mysql database and do processing over each record. Any pointers?


P.S. : The size of the table is very large, hence everytime, I can not compute the number of records in the table



Privacy Policy - Copyrights Notice - Feedback - Report Violation - RSS 2017 © bighow.org All Rights Reserved .