Apothem
05-10-2009, 12:55 AM
Here's what I want to do:
I want to insert into a table values with an unique ID and an unique URL.
At the same time, if I'm trying to insert a row into a table, I want to check if the URL exists and if it does, replace values... with the absence of the ID.
The problem with this is that if I REPLACE a duplicate URL into that table, it auto-increases the ID by 1 - which I wanted to be left alone.
Here's the table:
CREATE TABLE IF NOT EXISTS `data` (
`id` int(20) unsigned NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '', # True URL to the site
`cache` text, # Other data saved
`views` int(11) unsigned NOT NULL default '0',
`time` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`url`),
INDEX (`url`),
INDEX (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Now here's what it will have:
1 | http://google.com/ | 1 | 1 | 122954032
Query ran:
REPLACE INTO `data` (`url`, `cache`, `views`, `time`) VALUES ('http://google.com/', '5', '2', '122958035')
Result:
2 | http://google.com/ | 5 | 2 | 122958035
Wanted result:
1 | http://google.com/ | 5 | 2 | 122958035
I want to insert into a table values with an unique ID and an unique URL.
At the same time, if I'm trying to insert a row into a table, I want to check if the URL exists and if it does, replace values... with the absence of the ID.
The problem with this is that if I REPLACE a duplicate URL into that table, it auto-increases the ID by 1 - which I wanted to be left alone.
Here's the table:
CREATE TABLE IF NOT EXISTS `data` (
`id` int(20) unsigned NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '', # True URL to the site
`cache` text, # Other data saved
`views` int(11) unsigned NOT NULL default '0',
`time` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`url`),
INDEX (`url`),
INDEX (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Now here's what it will have:
1 | http://google.com/ | 1 | 1 | 122954032
Query ran:
REPLACE INTO `data` (`url`, `cache`, `views`, `time`) VALUES ('http://google.com/', '5', '2', '122958035')
Result:
2 | http://google.com/ | 5 | 2 | 122958035
Wanted result:
1 | http://google.com/ | 5 | 2 | 122958035