[MySQL 5.0.45-community-nt-log] How do I insert a random value into mysql?

[cms9651]

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
);

[guido2004]

Try LIMIT 1.
LIMIT 3 gives you 3 results, you can't save multiple values in one column of one row.

[cms9651]

thank you for help.

I tried this query, but I have error:
[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 'SELECT
strName

INSERT INTO listing (
	hits1,
	hits2,
	hits3
)(
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND()
	LIMIT 1
,
 
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND()
	LIMIT 1
,
 
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND()
	LIMIT 1
);

[r937]

remove the parentheses around the SELECT statements

[cms9651]

thank you for help.

Your suggestion:

remove the parentheses around the SELECT statements

I tried this query, but I have new error:
[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 'SELECT
strName
FROM
listing_names
ORDER BY
RAND
LIMIT 1

INSERT INTO listing (
	hits1,
	hits2,
	hits3
)
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND
	LIMIT 1
,
 
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND
	LIMIT 1
,
 
	SELECT
		strName
	FROM
		listing_names
	ORDER BY
		RAND
	LIMIT 1
;

[cms9651]

I tried this other solution, I don't have error but the output is:

ID	hits1	hits2	hits3
1	Fer	Fer	Fer
2	Myr	Myr	Myr
3	Vin	Vin	Vin

and not:

ID	hits1	hits2	hits3
1	Fer	Her	Esp
2	Myr	Gar	Bas
3	Vin	Gag	Pac


INSERT INTO listing (
	hits1,
	hits2,
	hits3
)
	SELECT
		strName AS C1, 
                strName AS C2, 
                strName AS C3
	FROM
		listing_names
	ORDER BY
		RAND()
	LIMIT 3;

[SpacePhoenix]

Is the insert query just being run once or many times? What's the server-side processing language being used (eg PHP, ASP, .NET etc)?

[cms9651]

thank you for help.

this is the solution:

SELECT x.strname hits1
     , y.strname hits2
     , z.strname hits3 
  FROM listing_names x
  JOIN listing_names y
    ON y.id <> x.id
  JOIN listing_names z 
    ON z.id <> x.id
   AND z.id <> y.id
 ORDER 
    BY RAND() LIMIT 3;


ID	hits1	hits2	hits3
1	Fer	Her	Esp
2	Myr	Gar	Bas
3	Vin	Gag	Pac