CODEWITHSUNDEEP
mysqlstored-procedures
CodeCrisis
CodeCrisis

Reputation: 85

Mysql UPDATING statement in datetime

i know its kind of weird but mind if somebody there look for this...

i have a 2 table which is

tableA
id | name | datetime -> format is int(11), varchar(128) datetime()
1 | 'foo bar' | '2014-08-04 00:53:16''

tableB
id | name | datetime | -> format is the same
1 | 'foo bar ' | null

using this statement
UPDATE tableA a inner join tableB b on b.name = a.name SET a.datetime = b.datetime
its say
Error Code: 1292. Truncated incorrect time value: '840:53:16';
what the... :)

if i select this using its own format
select * from tableA a inner join tableB b on b.name = a.name
its show
id | name | datetime |id | name | datetime |
1 | 'foo bar' | '2014-08-04 00:53:16'' | 1 | 'foo bar ' | null
am i doing something wrong?? if i use show create table tableA CREATE TEMPORARY TABLElist_of_in( idint(11) NOT NULL, namevarchar(128) NOT NULL, datetimedatetime NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

show create table tableB CREATE TEMPORARY TABLElist_of_in( idint(11) NOT NULL, namevarchar(128) NOT NULL, datetimedatetime NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

by the way , im creating store procedure right now and its working in my localhost
but when i try it to the server, its not working and, after 6 hours i found out
that this is the part that is not working...

by the way thanks for reading eheheh :))

Upvotes: 0

Views: 143

Answers (1)

TinBane
TinBane

Reputation: 936

I can't replicate your problem.

I recreated your tables, and when I run your query, it replaces tableA's datetime with the null from tableB. I'm guessing that you've built a simplified model of what is actually happening in your database? If that's the case, then the model you've made no longer highlights the problem.

What I did notice though, is that the error you are getting, is suggesting that the date format for one field doesn't match the other. I would check that both fields are in fact datetime, and that one isn't actually a varchar with the date in it.

Here's the dump of the sql to create my testing environment. If you run this (which will wipe TableA and TableB BTW), then run the query yourself, it should succeed. 

# Dump of table tableA
# ------------------------------------------------------------

DROP TABLE IF EXISTS `tableA`;

CREATE TABLE `tableA` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `tableA` WRITE;
/*!40000 ALTER TABLE `tableA` DISABLE KEYS */;

INSERT INTO `tableA` (`id`, `name`, `datetime`)
VALUES
    (1,'foobar','2014-08-04 00:53:16');

/*!40000 ALTER TABLE `tableA` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table tableB
# ------------------------------------------------------------

DROP TABLE IF EXISTS `tableB`;

CREATE TABLE `tableB` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `tableB` WRITE;
/*!40000 ALTER TABLE `tableB` DISABLE KEYS */;

INSERT INTO `tableB` (`id`, `name`, `datetime`)
VALUES
    (1,'foobar',NULL);

/*!40000 ALTER TABLE `tableB` ENABLE KEYS */;
UNLOCK TABLES;

Upvotes: 1

Related Questions