Query Question mysql

[Hadeszeus 15]

I want to add new character variable to all existing char_id in char_reg_num_db using sql query.

How I am going to do that? If my key = limit_var , index = 0, and value = 1

 

THanks!

[jaBote 438]

Never done anything like this, but if you feel like experimenting try the cursors example here: http://www.databasejournal.com/features/mysql/mysql-cursors-and-loops.html

 

Of course, experiment as much as you want in a backup or a test DB . The query you want to use for each cursor repetition is like this:

 

INSERT INTO `char_reg_num_db` (`char_id`, `key`, `index`, `value`) VALUES (EVERY_CHAR_ID_GOES_HERE, 'limit_var', '0', '1');

 

Or you could get even smarter and do something like this (totally untested) code:

-- Copy structure of char_reg_num_db into a temporary tableCREATE TABLE IF NOT EXISTS `temp_table` (  `char_id` int(11) unsigned DEFAULT '0',  `key` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '',  `index` int(11) unsigned DEFAULT '0',  `value` int(11) DEFAULT '0',  PRIMARY KEY (`char_id`,`key`,`index`),  KEY `char_id` (`char_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;-- Add all existing char_ids into our tableINSERT INTO `temp_table` (`char_id`) SELECT `char_id` FROM `char`;-- Add the data we want to add for every user (row in the table)UPDATE `temp_table` SET `key` = 'limit_var', `index` = '0', `value` = '1' WHERE 1;-- Insert the temporary data into our char_reg_num_db tableINSERT INTO `char_reg_num_db` SELECT * FROM `temp_table`;-- Drop our temporary tableDROP TABLE `temp_table`;

 

 And that's it. Hope it works. You should restart the server or make all your users relog for this to take effect.

[Hadeszeus 15]

I will try this thanks jaBote +1