SQL-Query for updating a value based on timestamp of another table




I have the following problem: I want to update the column "UPDATE_VALUE" of Table A with values from the column "SOURCE_VALUE" from anotherTable B. Both tables have a timestamp column, and the value "update_value" that should be updated inTable A should be updated with the value "source_value" of the row of Table B with the highest timestamp smaller/equal than the timestamp of the row of table A.

Here is my example:

Code: create table if not exists TableA (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),time_a DATETIME DEFAULT NULL, update_value VARCHAR(39)); insert into TableA(time_a) value ('2010-09-23 10:00:00'); insert into TableA(time_a) value ('2010-09-25 05:20:00'); insert into TableA(time_a) value ('2010-09-25 18:10:30'); insert into TableA(time_a) value ('2010-09-27 07:04:30'); create table if not exists TableB (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),time_b DATETIME DEFAULT NULL, source_value VARCHAR(39)); insert into TableB(time_b,source_value) value ('2010-09-23 00:00:00','VAL_1'); insert into TableB(time_b,source_value) value ('2010-09-24 12:30:00','VAL_2'); insert into TableB(time_b,source_value) value ('2010-09-24 04:50:00','VAL_3'); insert into TableB(time_b,source_value) value ('2010-09-25 12:00:00','VAL_4'); insert into TableB(time_b,source_value) value ('2010-09-25 12:20:00','VAL_5'); insert into TableB(time_b,source_value) value ('2010-09-26 01:50:00','VAL_6'); insert into TableB(time_b,source_value) value ('2010-09-26 02:00:00','VAL_7'); insert into TableB(time_b,source_value) value ('2010-09-27 14:00:00','VAL_8'); insert into TableB(time_b,source_value) value ('2010-09-28 22:00:00','VAL_9'); select * from TableA; +----+---------------------+--------------+ | id | time_a | update_value | +----+---------------------+--------------+ | 1 | 2010-09-23 10:00:00 | NULL | | 2 | 2010-09-25 05:20:00 | NULL | | 3 | 2010-09-25 18:10:30 | NULL | | 4 | 2010-09-27 17:04:30 | NULL | +----+---------------------+--------------+ select * from TableB; +----+---------------------+--------------+ | id | time_b | source_value | +----+---------------------+--------------+ | 1 | 2010-09-23 00:00:00 | VAL_1 | | 2 | 2010-09-24 12:30:00 | VAL_2 | | 3 | 2010-09-24 04:50:00 | VAL_3 | | 4 | 2010-09-25 12:00:00 | VAL_4 | | 5 | 2010-09-25 12:20:00 | VAL_5 | | 6 | 2010-09-26 01:50:00 | VAL_6 | | 7 | 2010-09-26 02:00:00 | VAL_7 | | 8 | 2010-09-27 14:00:00 | VAL_8 | | 9 | 2010-09-28 22:00:00 | VAL_9 | +----+---------------------+--------------+

So in row 1 of table A the I want VAL_1 of table B as the timestamp of this row is the highest of table B that is smaller/equal than the timestamp of the row in table A. For row 2 of table A I want row 3 of table B and so on. The complete result should look like this:

Code: +----+---------------------+--------------+ | id | time_a | update_value | +----+---------------------+--------------+ | 1 | 2010-09-23 10:00:00 | VAL_1 | | 2 | 2010-09-25 05:20:00 | VAL_2 | | 3 | 2010-09-25 18:10:30 | VAL_5 | | 4 | 2010-09-27 17:04:30 | VAL_8 | +----+---------------------+--------------+

I have tried the following:
Code: update TableA set update_value = (select source_value from TableB where TableA.time_a
Posted On: Monday 29th of October 2012 05:38:28 AM Total Views:  222
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Error when updating database

I'm just starting out learning how to do a little bit of programming. I'm following this guide: http://www.homeandlearn.co.uk/csharp/csharp_s12p11.html I have made an "update" button, where I want to update current existing fields, but I gettwo error messages saying: Error1The best overloaded method match for 'System.Data.OleDb.OleDbCommandBuilder.OleDbCommandBuilder(System.Data.OleDb.OleDbDataAdapter)' has some invalid argumentsC:\Users\Klaus\Documents\Visual Studio 2008\Projects\Databaseworker\Databaseworker\Form1.cs14618Databaseworker and Error2Argument '1': cannot convert from 'System.Data.SqlClient.SqlDataAdapter' to 'System.Data.OleDb.OleDbDataAdapter'C:\Users\Klaus\Documents\Visual Studio 2008\Projects\Databaseworker\Databaseworker\Form1.cs14660Databaseworker Here's the code for the database: I hope someone can help me out - thanks in advance! using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace Databaseworker { public partial class Fir : Form { public Fir() { InitializeComponent(); } System.Data.SqlClient.SqlConnection con; System.Data.SqlClient.SqlDataAdapter da; DataSet ds1; int MaxRows = 0; int inc = 0; private void Form1_Load(object sender, EventArgs e) { con = new System.Data.SqlClient.SqlConnection(); ds1 = new DataSet(); con.ConnectionString="Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Klaus\\Documents\\Visual Studio 2008\\Projects\\tblWorkers\\tblWorkers\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; con.Open(); string sql = "SELECT * From tblWorkers"; da = new System.Data.SqlClient.SqlDataAdapter(sql, con); MessageBox.Show("Database Open"); da.Fill(ds1, "Workers"); NavigateRecord(); MaxRows = ds1.Tables["Workers"].Rows.Count; con.Close(); MessageBox.Show("Database Closed"); } private void NavigateRecord() { DataRow dRow = ds1.Tables["Workers"].Rows[inc]; textBox1.Text = dRow.ItemArray.GetValue(1).ToString(); textBox2.Text = dRow.ItemArray.GetValue(2).ToString(); textBox3.Text = dRow.ItemArray.GetValue(3).ToString(); } private void btnNext_Click(object sender, EventArgs e) { if (inc != MaxRows - 1) { inc++; NavigateRecord(); } else { MessageBox.Show("No More Rows"); } } private void btnPrevious_Click(object sender, EventArgs e) { if (inc > 0) { inc--; NavigateRecord(); } else { MessageBox.Show("First Record"); } } private void btnLast_Click(object sender, EventArgs e) { if (inc != MaxRows - 1) { inc = MaxRows - 1; NavigateRecord(); } } private void btnFirst_Click(object sender, EventArgs e) { if (inc != 0) { inc = 0; NavigateRecord(); } } private void btnAddNew_Click(object sender, EventArgs e) { textBox1.Clear(); textBox2.Clear(); textBox3.Clear(); } private void btnSave_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlCommandBuilder cb; cb = new System.Data.SqlClient.SqlCommandBuilder(da); DataRow dRow = ds1.Tables["Workers"].NewRow(); dRow[1] = textBox1.Text; dRow[2] = textBox2.Text; dRow[3] = textBox3.Text; ds1.Tables["Workers"].Rows.Add(dRow); MaxRows = MaxRows + 1; inc = MaxRows - 1; da.Update(ds1, "Workers"); MessageBox.Show("Entry Added"); } private void textBox2_TextChanged(object sender, EventArgs e) { } private void textBox1_TextChanged(object sender, EventArgs e) { } private void btnUpdate_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbCommandBuilder cb; cb = new System.Data.OleDb.OleDbCommandBuilder(da); System.Data.DataRow dRow2 = ds1.Tables["Workers"].Rows[inc]; dRow2[1] = textBox1.Text; dRow2[2] = textBox2.Text; dRow2[3] = textBox3.Text; da.Update(ds1, "Workers"); MessageBox.Show("Data Updated"); } } }
VIEWS ON THIS POST

256

Posted on:

Friday 5th October 2012
View Replies!

Crontab and updating mysql

Hi Guys, I'll try to explain this the best way i can. I would like a cron that takes the servers current date, then scans my database and removes all entries which have a date field for prior to my server date. For example server date is 02/01/2008 I would like it to scan my database and remove any entries that have in the date field 01/01/2008 I have no real idea where to start and a bit new to mysql and crons etc so please be gentle with me
VIEWS ON THIS POST

138

Posted on:

Friday 12th October 2012
View Replies!

Auto updating of fields via triggers

What I have is a standard hierarchy where each level has a value. I would like to pre-calculate the SUM(value+total) of the child elements. What is the most efficient way of doing this I'm thinking of using triggers. I would expect to have two tables (because of the update limitation within MySQL triggers) Table 1: id_node int(10), id_parent int(10), value int(10) where id_parent = id_node of parent. Table 2: id_node int(10), total int(10) where total = SUM(value + total) for all children of id_node. The trigger would adjust the total field by eitherrecalculating the children (any ideas on strategy here)adjusting the current value of total by the difference (would two updates in the same locale possibly cause incorrect calcuation)Any thoughts/comments
VIEWS ON THIS POST

145

Posted on:

Friday 12th October 2012
View Replies!

Error 1111 when updating

I have 2 tables, 1 to hold updates in statistics for callers, and 1 to hold the totals. mysql> create table pg2( -> agent varchar(45) not null, -> month varchar(13) not null, -> monthgoal int(5), -> needDay int(3), -> pctMonth float(5,3), -> goalTD int(3), -> soFar int(5), -> shortOver int(5)); mysql> create table pg2ttl -> month varChar(13) n -> monthgoalT int(5), -> needDayT int(4), -> pctMonthT float(5,3 -> goalTDT int(5), -> soFarT int(6), -> shortOverT int(6)); both created with no issues. then I made a trigger to update pgTTL mysql> create trigger goals before update on pg2 for each row -> begin -> update pg2TTl set monthgoalT=sum(monthgoal); -> update pg2ttl set needDayt=monthgoalT/23; -> update pg2ttl set sofarT=sum(soFar); -> update pg2ttl set pctMonthT=soFarT/monthGoalT; -> update pg2ttl set goalTDT=sum(GoalTD); -> update pg2ttl set shortOverT=sum(shortOver); -> end; -> // Query OK, 0 rows affected (0.00 sec) i inserted some test data mysql> insert into pg2(agent, month, MonthGoal, needDay,pctmonth,goaltd,sofar,shortover) values -> ('Holly', 'February', 350, 15, 0.052, 18, 29, 11); Then tried to update in order to test the trigger, and that's where I get the error mysql> update pg2 set monthGoal=350, needDay=15, pctmonth=0.089, goalTD=31, sofar=40,shortover=9 where agent='Holly'; ERROR 1111 (HY000): Invalid use of group function can anyone tell me what i've done wrong. I've tried to update just a single column and i tried creating a primary key using an integer and using it to update but both times I get the same error. I also found that sometimes using the having statement instead of where will fix the error, but I haven't been able to get it to work yet. any help would be greatly appriciated
VIEWS ON THIS POST

147

Posted on:

Friday 12th October 2012
View Replies!

Need some help with checking then updating a table

Morning all, I have a form which fills a 'vehicledata' table with a dataitem and datatype dataitem = saab, datatype = manufacturer dataitem = petrol, datatype = fueltype dataitem = automatic, datatype = transmission etc.. I then I have a form through which a vehicle is added to a 'vehicles' table, with the vehicle data being picked from the vechicledata table. The 'vechicles' table has a colum for each of the datatypes above. I'm now working on an edit function, so that the names of the dataitems can be edited (if a spelling mistake was made etc). I need to check the 'vehicles' table, to warn the user that a dataitem is being used, and the dataitem will be updated. (and then updating any incorrect instances to the new dataitem name). But I'm stuck with the following: This means checking the whole ''vehicles' table (looping through each datatype column) for the dataitem, and then updating the whole table. I can't work out how to do this! Does anyone have any suggestions Or maybe I'm going about it in the wrong way!
VIEWS ON THIS POST

148

Posted on:

Friday 12th October 2012
View Replies!

Error: Row cannot be located for updating

I am running VB6, Microsoft ADO 2.5, MySQL 5.0, and "DRIVER={MySQL ODBC 3.51 Driver};". (My experience is with VB6, and the Microsoft databases.) Most of the code I have written used so far works but I am getting an error from code that I would expect to work. ** Error (on line with "rsTaps.Update" ***************** Run-time error -2147217864(80040e38): Row cannot be located for updating. Some values may have been changed since it was last read. ** Code ********* Set ConMySQL = New ADODB.Connection ConMySQL.CursorLocation = adUseClient ConMySQL.Mode = adModeReadWrite ConMySQL.Open strConnMySQL Set rsTaps = New ADODB.Recordset rsTaps.Open "SELECT * " _ & "FROM Taps ", _ ConMySQL, adOpenKeyset, adLockPessimistic intTF = -1 Do While Not rsTaps.EOF rsTaps("Flag2") = intTF rsTaps.Update rsTaps.MoveNext Loop rsTaps.Close: Set rsTaps = Nothing ConMySQL.Close: Set ConMySQL = Nothing ************************ I have simplified the code for clarity. The value of intTF is actually determined by another table look-up. So I have a reason for wanting to iterate through the table. How can I make this code run without error
VIEWS ON THIS POST

211

Posted on:

Friday 12th October 2012
View Replies!

Method of updating only new records

Hi Guys, I am not sure if this is possible but here goes what I have and what I am trying to do... Table 1 is like this... Code: +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | CLIENT | varchar(255) | YES | MUL | NULL | | | CODE | varchar(255) | YES | | NULL | | | DATE | date | YES | | NULL | | | TIME | varchar(255) | YES | | NULL | | | PHONE | varchar(255) | YES | | NULL | | | RESULT | varchar(255) | YES | | NULL | | | CHARGE | decimal(8,2) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ Which has no index and even creating an index across all the fields still doesn't make a record unique because some records are identical. Then I have table 2 which is generated from a CSV file which is identical to table 1 except that it may have newer records. I would like to know if it is possible to somehow insert these new records that do not exist in table 1, into table 1 but at the same time maintain any edits that may have taken place in table 1 (meaning I can not simply do a delete on table 1 and insert everything from table 2). The lack of an index is really doing my nut because I would be able to do this otherwise, is it even possible without one
VIEWS ON THIS POST

197

Posted on:

Friday 12th October 2012
View Replies!

updating with constraint, question

Say I have the two following tables. Code mysql: create table parent ( name varchar (24) not null , PRIMARY KEY (name) ) engine-InnoDB DEFAULT CHARSET=latin1; INSERT INTO parent (name) VALUES ('johnny'), ('paul'), ('malcolm'); create table child ( id int not null auto_increment , name varchar (24) , PRIMARY KEY (id) , CONSTRAINT child_parent_fk FOREIGN KEY (name) REFERENCES parent (name) ON UPDATE CASCADE ) engine-InnoDB DEFAULT CHARSET=latin1; INSERT INTO child (id, name) VALUES ('', 'johnny'), ('', 'paul'), ('', 'malcolm'); Poor choice of data for this example but; if I have to make all the 'malcolm' be 'paul', if I do this in the parent table, I would get an error because of the Primary Key issue. If I delete 'malcolm' there is nothintg in that deletion to tell the child 'malcolm's to update to paul. Is there no shorter way than to write a script which will 1. rename all malcolms to paul, in the child table 2. delete malcolm from the parent table. ho hum. bazz
VIEWS ON THIS POST

147

Posted on:

Sunday 21st October 2012
View Replies!

updating multiple rows in one query

, tried to find the answer with search but didn't return any answers. OK, here is the table Code: table test ------------------------ | test_id | test_order | ------------------------ | 1 | 1 | ------------------------ | 2 | 2 | ------------------------ | 3 | 3 | ------------------------ I'm trying to change the orders in one query, but not sure how to do that. phpMyAdmin shows me the code like this $sql = 'UPDATE `test` SET `test_order` = \'2\' WHERE `test_id` = 1;' 'UPDATE `test` SET `test_order` = \'3\' WHERE `test_id` = 2;' 'UPDATE `test` SET `test_order` = \'1\' WHERE `test_id` = 3;' . ' '; I'v tried that but got a syntax error. MySQL version is 4.0.26, can anyone help please
VIEWS ON THIS POST

155

Posted on:

Sunday 21st October 2012
View Replies!

updating a field after certain time

i am using mysql and i have field like update showvalue=1,updatetime=CURDATE() where value=value_id and what i want is after 2 minutes processing that query...and changing value the value of showvalue turned to 0 ie update showtime=0 where curtime-updatedatetime>2 minutes....it will be in the page which keeps on upgrading so no problem for its excution... so here problems are 1)how to correctly formulate that second query 2)as the condition will be always true after 2 minutes it will always update which i dont want ..so may be 1 time update after 2 seconds and no excution after that...unless that value is changed ..so how can i do that...
VIEWS ON THIS POST

157

Posted on:

Monday 22nd October 2012
View Replies!

Continually updating one database from another

, I have a client who, for whatever reason wants 2 databases. Basically, they have some functionality on one, and want to keep it, though I don't know what it is. Anyway, they will be downloading from database 1 and uploading to database 2 a couple times a month. I was wondering how I could make this happen without creating duplicate fields. I guess it would go by the primary key. But, since I have never had to do this, I am unsure.
VIEWS ON THIS POST

290

Posted on:

Wednesday 24th October 2012
View Replies!

Group distinct rows values to columns

I have a table that records the sales of several shops like this site|date|mount shop1|2012-04-04 08:05:02|10.5 shop1|2012-04-04 08:05:02|2.00 shop2|2012-04 08:05:02|7.50 shop1|2012-04-05 08:05:02|6.00 shop1|2012-04-05 08:05:02|9.00 shop2|2012-04-06 08:05:02|10.00 shop2|2012-04-06 08:05:02|3.00 I want make a report that shows on the columns the distinct dates of period and the distinct shops on the rows. site|2012-04-04|2012-04-05|2012-04-06 shop1|12.50|13.00|0 shop2|7.50|0.00|13.00 It's obvious that I have group by date by date and shops but I need help to do it cause I don't really know how.
VIEWS ON THIS POST

119

Posted on:

Monday 29th October 2012
View Replies!

Switching values between rows in MySQL.

Alright, so I am making a Pokemon RPG (Text "PHP" based, highly reliant on MySQL). I am having a bit of trouble with adding Pokemon to one's roster. Now a Pokemon roster needs to consist of 6 Pokemon. In my database, I have a field, 'roster' which defines which Pokemon is in the roster, and which Pokemon is not. Roster values are defined by numbers 1-6, where 1 is the first roster slot, 2 is the second, and so on. Pokemon not in the roster are simply defined as roster='0'. I want to be able to switch a Pokemon from the box, with any Pokemon in the roster. Here is what I have, but I notice that due to the 'AND pokemon_id="$poke_id"' part, only one row is being edited. The pokemon_id is an auto incremented ID that is unique to each Pokemon. So here is the part that is supposed to switch the values: PHP Code: if(isset($_GET['id2'])){ $new_position=$_GET['id2']; $old_position=$_GET['slot']; $poke_id=$_GET['pokemon']; if(!empty($_GET['id2'])){ $new_position=$_GET['id2']; $old_position=$_GET['slot']; $poke_id=$_GET['pokemon']; mysql_query("UPDATEpokemon2SETroster=(CASEWHENroster=$old_positionTHEN'$new_position'WHENroster=$new_positionTHEN'$old_position'END)WHERErosterIN('$old_position','$new_position')ANDuser_id='{$_SESSION['user_id']}'ANDpokemon_id='$poke_id'"); } } And now here is the box (from where the Pokemon is added to the roster): PHP Code:
VIEWS ON THIS POST

124

Posted on:

Monday 29th October 2012
View Replies!

Obtain day of week from datetime value in mysql

edit: ok figured it out i used 'weekday' instead of 'hour' but the results show 0 for sunday and 7 for saturday, what function can i use to convert this or display it in the weekday format instead of numbers I have the following query that shows the hours that the entries were added but would like to know how I show the days of the week. Code: SELECT hour( `datetime_added` ) AS h, count( hour( `datetime_added` ) ) AS c FROM `requests` GROUP BY h ORDER BY h would it be that i just change the 'hour' to 'day' or is there more to it. the date time format is.. 2011-04-07 16:10:00 Y-m-d H:i:s
VIEWS ON THIS POST

179

Posted on:

Monday 29th October 2012
View Replies!

View returnn zero but on ther pc return values but the same tables.

I a have problem with mysql ,I already made database with 6 or 7 tables and 3 views , inserted rows in all tables ,so the three views return resaults according to tables content. when I export the database and import it on other pc every thing run ok,but one of the views didn't return values ....I don't know why..but this is what is happining. It is not logic.the think that I didn't minthind that the exporter OS is Linux ubuntu and the importer is Windows 7 so the versions of mysql is different from each other. any solution for this problem.
VIEWS ON THIS POST

302

Posted on:

Monday 29th October 2012
View Replies!

Checking if a values exists in multiple tables

everybody. I ran into a dead end trying to figure out how to produce a query that would check if a value exists in multiple tables, in my case 3 tables to be specific. I got 3 tables (user, partner, staff) each one with 1 primary key that is common on all of them (iduser, idpartner, idstaff). I want to list all the users that ARE NOT present on the tables staff and partner but are present on the table user. Here's an example of my tables (remove the space on the link) http:// i54.tinypic.com/15i6rz5.png Here's an example of what i want to list (remove the space on the link) http:// i54.tinypic.com/viecg3.png This is what i have got but it will not work. Code: SELECT usr.iduser, pat.idpartner, sta.idstaff FROM user usr, partner pat, staff sta WHERE pat.idpartner IS NULL AND sta.idstaff IS NULL AND usr.iduser IS NOT NULL
VIEWS ON THIS POST

150

Posted on:

Monday 29th October 2012
View Replies!

Totalling select values of a column...

I want to add together certain values of a column called 'order_quan' This column represents the quantity of a product bought. There is also a 'session_id' column in this table, the same session id is repeated for products which were bought in that session. E.G session_id---------product_id---order_quan 52454543345------1-----------------2 52454543345------4-----------------1 52454543345------7-----------------3 52454543345------2-----------------5 34274293849------3-----------------2 34274293849------5-----------------1 So in session '52454543345', a person bought a total of 11 products in session '34274293849', a person bought a total of 3 products I'm trying to get an sql query which works this out for me so I can display the values in a table. It's the whole linking to the session_id I'm stuck on. I know I can't just do "where session_id='53434534'" as the session id will change every time Any ideas
VIEWS ON THIS POST

162

Posted on:

Monday 29th October 2012
View Replies!

Insert with select and additional values query problem

Can I request some guidelines. I am trying to insert 2 values from a table in to another table and in addition am trying to pass 3 other values. The below query gives an error. Code: insert into tblverify (contactID_vfy,contactname_vfy,verifier,verify_date,verify_action) values ((select contactID,contactname from tblcontactname where contactID = '14'),'1',now(),'VERIFY') Error : #1136 - Column count doesn't match value count at row 1 PS: The table tblverify has a primary auto_number ID field in addition to above which is not included in the above query.
VIEWS ON THIS POST

136

Posted on:

Monday 29th October 2012
View Replies!

Need help SQL select rows containing value

I have rows containg : row1 : 1 row2 : 1 row3 : 2,3,4 row4 : 3,5,7 row5 : 2 ... how to select the rows containg for example 2 ( row3 and row5 ) Select * FROM 'table' WHERE $value IN 'row'; - something like that. I say $value, because i have to do it in PHP script, but any help would be greatly appreciated // My english is not so good, so it's hard to search for something that you can't explain ( for those that will tell me to just Google for it ), i tried believe me
VIEWS ON THIS POST

93

Posted on:

Monday 29th October 2012
View Replies!

Data truncation: Truncated incorrect datetime value

I'm using "load data infile" and I'm getting this error: Data truncation: Truncated incorrect datetime value: '2010-10-14 09:07:37' It doesn't happen all the time, there are files that are successfully inserted. but every few minutes there is one file that cause that error. The command I'm using is: LOAD DATA INFILE '/mnt/log.csv' INTO TABLE logs FIELDS TERMINATED BY '!%$,$%!' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '!%$##$%!' (a, b, c, d, e, f, Time) ; And I tried also this one: LOAD DATA INFILE '/mnt/log.csv' INTO TABLE logs FIELDS TERMINATED BY '!%$,$%!' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '!%$##$%!' (a, b, c, d, e, f, @Time) set Time=str_to_date(@Time,'%Y-%m-%d %h:%i:%s'); the file look like this: \N!%$,$%!127.0.0.1!%$,$%!\N!%$,$%!127.0.0.1!%$,$%!34 /534.3!%$,$%!0.0!%$,$%!2010-10-14 06:07:31!%$##$%!\N!%$,$%!127.0.0.1!%$,$%!\N!%$,$%!127.0.0.1!%$,$%!34 /534.3!%$,$%!0.0!%$,$%!2010-10-14 06:07:31 any ideas
VIEWS ON THIS POST

195

Posted on:

Monday 29th October 2012
View Replies!