hello.
It looks like RAND is what I need but I’m having a bit of trouble understanding how it works.
I need to insert a random name between 1 and 3 into a table mysql, selecting these random name from a first table called listing_names.
Table name is: listing and the column name is: hits
I tried this query but error is:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’
Could you please help?
Thanks you very much for your help.
ID hits1 hits2 hits3
1 Fer Her Esp
2 Myr Gar Bas
3 Vin Gag Pac
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `listing_names`
-- ----------------------------
DROP TABLE IF EXISTS `listing_names`;
CREATE TABLE `listing_names` (
`strName` varchar(255) DEFAULT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of listing_names
-- ----------------------------
INSERT INTO `listing_names` VALUES ('Ale', '1');
INSERT INTO `listing_names` VALUES ('Ana', '2');
INSERT INTO `listing_names` VALUES ('Cec', '3');
INSERT INTO `listing_names` VALUES ('Cru', '4');
INSERT INTO `listing_names` VALUES ('Fed', '5');
INSERT INTO `listing_names` VALUES ('Fer', '6');
INSERT INTO `listing_names` VALUES ('Her', '7');
INSERT INTO `listing_names` VALUES ('Esp', '8');
INSERT INTO `listing_names` VALUES ('Myr', '9');
INSERT INTO `listing_names` VALUES ('Gar', '10');
INSERT INTO `listing_names` VALUES ('Bas', '11');
INSERT INTO `listing_names` VALUES ('Vin', '12');
INSERT INTO `listing_names` VALUES ('Gag', '13');
INSERT INTO `listing_names` VALUES ('San', '14');
INSERT INTO `listing_names` VALUES ('Pac', '15');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `listing`
-- ----------------------------
DROP TABLE IF EXISTS `listing`;
CREATE TABLE `listing` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`hits1` varchar(255) DEFAULT NULL,
`hits2` varchar(255) DEFAULT NULL,
`hits3` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO listing (
hits1,
hits2,
hits3
)(
SELECT
strName
FROM
listing_names
ORDER BY
RAND()
LIMIT 3
,
SELECT
strName
FROM
listing_names
ORDER BY
RAND()
LIMIT 3
,
SELECT
strName
FROM
listing_names
ORDER BY
RAND()
LIMIT 3
);