Update statement causes fields to be updated with NULL or maximum value

Question

If you had to pick one of the two following queries, which would you choose and why:

UPDATE `table1` AS e
SET e.points = e.points+(
SELECT points FROM `table2` AS ep WHERE e.cardnbr=ep.cardnbr);

or:

UPDATE `table1` AS e
INNER JOIN
(
   SELECT points, cardnbr
   FROM `table2`  
) AS ep ON (e.cardnbr=ep.cardnbr)
SET e.points = e.points+ep.points;

Tables' definitions:

CREATE TABLE `table1` (
  `cardnbr` int(10) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `points` decimal(7,3) DEFAULT '0.000',
  `email` varchar(50) NOT NULL DEFAULT '[email protected]',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25205 DEFAULT CHARSET=latin1$$

CREATE TABLE `table2` (
  `cardnbr` int(10) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `points` decimal(7,3) DEFAULT '0.000',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

UPDATE: BOTH are causing problems the first is causing non matched rows to update into NULL. The second is causing them to update into the max value 999.9999 (decimal 7,3). PS the cardnbr field is NOT a key

Answer 1

I prefer the second one..reason for that is

When using JOIN the databse can create an execution plan that is better for your query and save time whereas subqueries (like your first one ) will run all the queries and load all the datas which may take time.

i think subqueries is easy to read but performance wise JOIN is faster...