[SOLVED] Joining 2 UPDATE queries into one

[Tomcenc]

Mysql version 5.0.22

Table structure (cropped to relevant columns):

CREATE TABLE `example` (
`id` mediumint( unsigned NOT NULL default '0',
`intiger` int(10) unsigned NOT NULL default '0',
`lastintigerchange` int(10) unsigned NOT NULL default '0',
`intigerrank` int(10) unsigned NOT NULL default '0'),
PRIMARY KEY (`id`)
ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TEMPORARY TABLE `exampletemp` (
`id` mediumint( 6 ) unsigned NOT NULL default \'0\',
`intiger` int( 10 ) unsigned NOT NULL default \'0\',
`intigerrank` mediumint( 6 ) unsigned NOT NULL default \'0\')
ENGINE = MEMORY DEFAULT CHARSET = latin1;

Queries:

UPDATE exampletemp,example SET example.intiger=exampletemp.intiger, example.lastintigerchange=[uNIX TIME] WHERE exampletemp.id=example.id&&exampletemp.intiger>example.intiger);

UPDATE exampletemp,example SET example.intigerrank=exampletemp.intigerrank WHERE exampletemp.id=example.id);

I'd like to join the two queries above into one query. As you can see, intiger and lastintigerchange only need to be updated when the intiger is larger (the intiger only increments), so I'd think I'd need some sort of if statement.

 

Something like this:

UPDATE exampletemp,example SET example.intigerrank=exampletemp.intigerrank, IF ((exampletemp.intiger>example.intiger),(example.intiger=exampletemp.intiger, example.lastintigerchange=[uNIX TIME]),'') WHERE exampletemp.id=example.id;

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

I can't figure out how to do it as it doesn't seem to execute statements.

[fenway]

IF() can only be used to return an RVALUE... you'll have to separate out each field.

[Tomcenc]

IF() can only be used to return an RVALUE... you'll have to separate out each field.

 

Thank you. Fairly stupid I didn't think about that. I changed it into this and it works:

UPDATE exampletemp,example SET example.intiger=exampletemp.intiger, example.intigerrank=exampletemp.intigerrank, example.lastintigerchange=IF(exampletemp.intiger>example.intiger, [uNIX TIME], example.lastintigerchange) WHERE exampletemp.id=example.id);