Re: Data masking for mysql
Hi, If you don't care to much how the data look like - just run once of update and replace it with some random stings. If you do however, and you still want to telephone numbers looks like telephone numbers, what I can suggest is to use a data generator like this one http://www.generatedata.com/ and generate sufficient amount of fake data, then run a simple select - update query or script and change data in your database. I was using that a couple of times when a UAT database was restored from production but 'cause of data sensitiveness I had to mask it a bit. Good luck! m Od: reena.kam...@jktech.com Do: mysql@lists.mysql.com; Wysłane: 8:57 Wtorek 2014-04-15 Temat: Data masking for mysql Hi, I need to do data masking to sensitive data exists in mysql db. is there any data masking tool available for mysql with linux platform. if yes... please provide the links. else... please suggest other alternatives for this requirement. I look forward to hearing from you. With best regards, Reena Kamboj ** CONFIDENTIALITY. This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose, or store or copy the information in any medium. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of JK Technosoft Ltd. If you have received this e-mail in error please immediately notify the person who has sent this email to you with copy to administra...@jktech.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Oracle Launches New MySQL 5.6 Certifications
Older MySQL Exams to Retire https://blogs.oracle.com/certification/entry/0875_01 regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: incorrect arithmetic calculation
From: Alex Keda ad...@lissyara.su To: mysql@lists.mysql.com; Sent: 6:37 Piątek 2013-02-15 Subject: MySQL 5.1: incorrect arithmetic calculation ( ... cut ...) but, my desktop calculator gives the result 548.60 1. your desktop calculator is wrong 2. correct result is 548.595, variations (548.59 and 548.594985) are related to various data types and rounding related issues while multiplying and dividing by 100 http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html Regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL refusing to accept passwords
Shawn Green (MySQL) shawn.l.gr...@oracle.com pisze: (...) You can be the correct user, using the correct password but you may not be allowed (by the host pattern) to login from the machine from which you are attempting to login. It could be the case you got your account setup as username@localhost and it works fine, as long as you're using 'localhost' in your Workench connection window. Once changed to an IP address or a host name and from MySQL server perspective - as Shawn said - you're a different user. Regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Efficiency of NULLIFie expression
h...@tbbs.net wrote: 2011/05/12 15:15 +0200, misiaq h...@tbbs.net wrote: (...) Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when houmuch and SharePrice are not equal? Try it yourself: mysql explain extended select * from table1; and then mysql show warnings; You will see the optimized query. http://dev.mysql.com/doc/refman/5.0/en/explain.html Not much of an explanation of EXPLAIN--and this command is mostly about indexing and joining, in SQL of course of immens importance, and, to be sure, finding houmuch in my query costs JOINing and SUMming, but I see nothing relevant to my question. On the other hand, maybe finding houmuch costs so much more than the rest of the expression that it does not matter. In any case, there is here no answer to my question. You're right about explain. That is why I suggested to use explain EXTENDED and then SHOW WARNINGS. Second query will show you the optimizer output. As far as I understand - that was the par of your question. Regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Efficiency of NULLIFie expression
h...@tbbs.net wrote: (...) Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when houmuch and SharePrice are not equal? Try it yourself: mysql explain extended select * from table1; and then mysql show warnings; You will see the optimized query. http://dev.mysql.com/doc/refman/5.0/en/explain.html Regards, m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1062 (23000): Duplicate entry '31592' for key 'PRIMARY'
Corrupted table and / or index. A number of reasons could cause this issue: http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tables.html Regards, m Adarsh Sharma adarsh.sha...@orkash.com pisze: Thanks , but there is no trigger on tables. Even I solved the problem after googling a link but cannot understand below mentioned things :- Please have some comments on them (... cut ...) It means there are 31581 rows but max(log_id suggests 10 more , how this could be possible, may be some rows deleted. I solved the error by repairing the table repair table login; mysql repair table login; +--++--++ | Table| Op | Msg_type | Msg_text | +--++--++ | attendance.login | repair | warning | Number of rows changed from 31581 to 31583 | | attendance.login | repair | status | OK | +--++--++ 2 rows in set (0.29 sec) Then all insert statements works . Can u please explain the root cause solution too. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Error in accept: Too many open files
Hi, It's quite simple and quick change in *nix. But would recommend to check your server performance first (processlist, tmp tables, slow query log etc). Regards, m Jerry Schwartz je...@gii.co.jp pisze: You are seeing *NIX error messages. You need to increase the operating system settings that control the number of files that a process can open. I'm very rusty, and never really used Linux, so I can't tell you the exact parameters. There are probably two: total number of open files, and files per process. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Brent Clark [mailto:brentgclarkl...@gmail.com] Sent: Tuesday, April 26, 2011 3:44 AM To: mysql mailing list Subject: Error in accept: Too many open files Hiya I recently imported data on a new server (5.1). But before the importing I added the feature 'innodb_file_per_table'. I now want to enable Master - Master replication, but the problem is, im seeing the following --- --- Apr 24 23:32:50 maj-web01 mysqld: 110424 23:32:50 [ERROR] Error in accept: Too many open files Apr 24 23:35:03 maj-web01 mysqld: 110424 23:35:03 [ERROR] /usr/sbin/mysqld: Can't open file: './maj_fs2/sites.frm' (errno: 24) --- --- Ive been going through High Performance MySQL, and a key area the book address is the config option 'innodb_open_files'. If I do a file count for *.ibd files, I only have 147 files, and I see the limit is 300 mysql show global variables like 'innodb_open_files'\G; *** 1. row *** Variable_name: innodb_open_files Value: 300 1 row in set (0.00 sec) Could the other option to look at be 'open_files_limit'? The database is a mixture of innodb and Myiasm. I really need to get replication working, if someone could help my understand this issue, it would be appreciated. Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mis...@poczta.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Data in Table Is Different When Seen From Different Sources
Hal Vaughan h...@halblog.com wrote: I'm having the strangest issue. I am using a Perl program to test out some other Perl programs and all the Perl connections with MySQL are normal, as in I use the standard interface. But in the test program I'm just using this: [... cut ...] Transaction isolation level? Add a timestamp column showing last update and then compare values. Regards, m - Sprawdz pogode. Kliknij http://linkint.pl/f2903 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: strange behavior in mysql-server 5.1.49 and 5.1.51
Works fine on 5.0.87 (rows returned as expected).
Confirmed on 5.1.51-log.
Most likely problem with VARCHAR behavior, because this one works fine:
select * from test_nkomp_admin where host_id=6 and trim(admin_id)='luke';
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mis...@poczta.fm
---
Pobierz slownik angielsko-polski na telefon!
Kliknij http://linkint.pl/f2839
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: strange behavior in mysql-server 5.1.49 and 5.1.51
Some more testing performed and it seems like problem with foreign key
reference indexing, see below:
create table test_nkomp_admin2
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade
)engine=innodb;
create table test_nkomp_admin3
(
host_id int unsigned,
admin_id varchar(15)
)engine=myisam;
insert into test_nkomp_admin2 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin2 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin2 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin2 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin2 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin2 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
insert into test_nkomp_admin3 (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin3 (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin3 (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin3 (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin3 (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin3 (host_id,admin_id) values
(6,'luke'),(6,'yoda');
mysql select * from test_nkomp_admin where host_id=6 and admin_id=yoda ;
Empty set (0,00 sec)
mysql select * from test_nkomp_admin2 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
mysql select * from test_nkomp_admin3 where host_id=6 and admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0,00 sec)
Regards,
m
-Original Message-
From: gregor kling [mailto:gregor.kl...@dvz.fh-giessen.de]
Sent: Thursday, October 28, 2010 1:54 PM
To: mysql@lists.mysql.com
Subject: strange behavior in mysql-server 5.1.49 and 5.1.51
Hello list,
I fight a strange behavior in mysql versions 5.1.49 and 5.1.51 -
maybe a fight against myself ;-)
The following query works exactly as assumed in version 5.1.41 with
the given testbed:
select * from test_nkomp_admin where host_id=6 and admin_id=yoda;
/* version 5.1.41 ubuntu 10.04 */
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | yoda |
+-+--+
1 row in set (0.00 sec)
whereas in versions 5.1.49 (ubuntu 10.10) and 5.1.51 (package from
debian experimental,os ubuntu 10.10) the resultset is:
mysql select * from test_nkomp_admin where host_id=6 and
admin_id=yoda ;
Empty set (0.00 sec)
prove:
select * from test_nkomp_admin where host_id=6 ;
+-+--+
| host_id | admin_id |
+-+--+
| 6 | luke |
| 6 | yoda |
+-+--+
2 rows in set (0.00 sec)
Could anyone prove this behavior, or can give hint what the problem
might be ?
/* testbed */
drop table if exists test_nkomp_admin;
drop table if exists test_nkomp;
drop table if exists test_admin;
create table test_nkomp
(
host_id int unsigned auto_increment not null primary key
)engine=innodb;
create table test_admin
(
admin_id varchar(15) not null primary key
)engine=innodb;
create table test_nkomp_admin
(
host_id int unsigned,
admin_id varchar(15),
foreign key (host_id) references test_nkomp (host_id) on delete
cascade on update cascade,
foreign key (admin_id) references test_admin (admin_id) on delete
cascade on update cascade
)engine=innodb;
insert into test_nkomp (host_id) values (1),(2),(3),(4),(5),(6);
insert into test_admin (admin_id) values
('luke'),('yoda'),('anakin'),('leia'),('r2'),('obi'),('han');
insert into test_nkomp_admin (host_id,admin_id) values
(1,'luke'),(1,'yoda'),(1,'anakin'),(1,'leia'),(1,'r2'),(1,'obi'),(1,'han');
insert into test_nkomp_admin (host_id,admin_id) values
(2,'luke'),(2,'yoda'),(2,'anakin'),(2,'leia'),(2,'r2'),(2,'obi');
insert into test_nkomp_admin (host_id,admin_id) values
(3,'luke'),(3,'yoda'),(3,'anakin'),(3,'leia'),(3,'r2');
insert into test_nkomp_admin (host_id,admin_id) values
(4,'luke'),(4,'yoda'),(4,'anakin'),(4,'leia');
insert into test_nkomp_admin (host_id,admin_id) values
(5,'luke'),(5,'yoda'),(5,'anakin');
insert into test_nkomp_admin (host_id,admin_id) values
(6,'luke'),(6,'yoda');
cheers
gregor
--
Mieszkania, domy, dzialki -
RE: MySQL SUM on two columns
I don't think that SUM will work for varchar columns. If I got your point right - you need to use WITH ROLLUP grouping http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html see second example on that page. regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 11:43 AM To: [MySQL] Subject: MySQL SUM on two columns Hi, I've the following basic SUM for our products based on a rating. SELECT SUM(products.rating) AS products_rating FROM products_sales INNER JOIN products ON products_sales.products_id = products.products_id WHERE products.enabled = 1 AND products_sales.language = 'EN' This works fine, however I also want to SUM the ES language within the same query. Is this possible to do, or should I just query the table twice, replacing the language parameter ? Thanks. Neil -- Saloon gier - to nas wyroznia! Sprawdz http://linkint.pl/f27e0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Retrieve three columns in sub query
select ci.*, ct.* from (select name, countrycode from city) ci, country ct where ci.countrycode = ct.code Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 1:23 PM To: [MySQL] Subject: Retrieve three columns in sub query Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- Kup wlasne mieszkanie za 72 tys. zl. Sprawdz najlepsze oferty http://linkint.pl/f27c4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: difference btw Analyze and Optimize table..
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html The entire page is highly recommended due to the number of helpful information. Regards, m -Original Message- From: F.A.I.Z.A.L [mailto:sac.fai...@gmail.com] Sent: Tuesday, April 13, 2010 10:56 AM To: River wubx Cc: mysql; mysql-help Subject: Re: difference btw Analyze and Optimize table.. hi River thanks for your mail. could you please tel me the correct syntax for optimize table command. i plan to put this in cronjob and execute every 8 hours in a day. optimize table tab1; is that correct or anything need to add with his statement. my environment is solaria Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Tue, Apr 13, 2010 at 2:37 PM, River wubx cnw...@gmail.com wrote: OPTIMIZE TABLE tablename; analyze table ,stores the key distribution for a table ,reclaim the unused space and to defragment the data file. ANALYZE TABLE tablename; This action only analyze table and stores the key distribution for a table. 2010-04-13 -- River wubx Gtalk: wubin...@gmail.com http://www.mysqlsupport.cn China -- *发件人：* F.A.I.Z.A.L *发送时间：* 2010-04-13 16:52:28 *收件人：* mysql; mysql-help *抄送：* *主题：* difference btw Analyze and Optimize table.. hi all what is the difference between OPTIMIZE TABLE tablename; and ANALYZE TABLE tablename; thank you Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Restricting data input
For such strongly database related logic sometimes its handy to keep all the update and insert queries inside the stored procedures. All the validation you mention in that case should be also build in the procedure. Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, April 06, 2010 11:42 AM To: [MySQL] Subject: Restricting data input Hi I have a table that contains league standings type information for a soccer league. For example season_id round_id team_id league_id home_won home_draw home_lost home_goals home_conceded away_won away_draw away_lost away_goals away_conceded Basically each row contains each league's round results for each team. From here I can display the league standings for each round for each league. My question is how / what is the best way to restrict incorrect data input apart from a coding issue/bug - e.g I need to ensure that for each round a team can only have either a value in the fields home_won or home_draw or home_lost or away_won or away_draw or away_lost as a example. Or is this best coded within my application ? Cheers Neil -- Szukasz pracy? Sprawdz oferty na praca.interia.pl! http://link.interia.pl/f265b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to update entire column with different values
You mean the SET data type..? http://dev.mysql.com/doc/refman/5.0/en/set.html Regards, m -Original Message- From: muralikrishna g [mailto:muralikrishn...@gmail.com] Sent: Friday, February 05, 2010 10:38 AM To: mysql@lists.mysql.com Subject: how to update entire column with different values hi.. i am in need to update a column with different values in a single query i know how to update a value of single column and single row element, and single row multiple columns. but i dont know how to update multiple values of a single column. if any body know the syntax for it please help me.. i am in need thanks in advance regards muralikrishn...@gmail.com -- Codziennie 11 tys. ofert pracy Sprawdz http://link.interia.pl/f25ac -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to update entire column with different values
Once you can group your rules into something reasonable you can use syntax like this one: UPDATE city SET cityClass = CASE WHEN population 1000 THEN 1 WHEN population 50 THEN 2 WHEN population 100 THEN 4 ELSE 3 END WHERE cityId between 1 and 200; But when each row got a separate logic - just execute multiple queries. Reegards, m -Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Friday, February 05, 2010 10:55 AM To: 'misiaQ' Subject: RE: how to update entire column with different values Whats the set data type I don't know... what I have to do is.. I have to update the years... for a entire column... like Update table1 Set year= if(id=1, then 2005) If(id=2, then 2003) like this... If any body know how to make it.. please help me -- Kup wlasne mieszkanie za 72 tys. zl. Sprawdz najlepsze oferty http://link.interia.pl/f25a8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: help me out for this problem...
Instead of: - else 2003 - where id between 1 and 6; Try - else 2003 - end - where id between 1 and 6; Or maybe even: mysql update table1 - set year=case when id IN (1, 2, 4) then 2000 - when id IN(2, 4, 6) then 2001 - else 2003 - end - where id between 1 and 6; Good luck! Regards, m -Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Friday, February 05, 2010 12:04 PM To: mysql@lists.mysql.com Subject: help me out for this problem... Here I have to update year column with reference to the row id mysql update table1 - set year=case when id=1 then 2000 - when id=2 then 2001 - when id=3 then 2000 - when id=4 then 2001 - when id=5 then 2000 - when id=6 then 2001 - else 2003 - where id between 1 and 6; ERROR 1064 (42000): 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 'where id between 1 and 6' at line 9 mysql -- Urocze domki... Zobacz http://link.interia.pl/f25aa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Update Doesn't Update!
If ID column is primary key and auto increment as you said, it cant be equal
to zero.
You got a query which reads:
UPDATE columns WHERE false
There is no chance for any updates.
http://dev.mysql.com/doc/refman/5.0/en/update.html
Regards,
m
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: 11 December 2009 10:06
Cc: mysql@lists.mysql.com
Subject: Re: Update Doesn't Update!
On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V
--
Szef przynudza? Zagraj sobie!
Sprawdz http://link.interia.pl/f24e4
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select through characters
Hi, If the efficiency is the key factor I would suggest to create a trigger on insert and update and mark the rows in a separate column instead of executing some fancy string checks during select. Regards, m. -Original Message- From: nikos [mailto:ni...@qbit.gr] Sent: 19 November 2009 08:41 To: mysql@lists.mysql.com Subject: Select through characters Hello list I have a list of names with english and greek characters. How can select them separately? I mean, only greeks or only english. thank you, Nikos -- Wst±p do Klubu Gracza, wygraj telefon! Sprawdz http://link.interia.pl/f243a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Alphabetical search to and from
Try this: select * from country where LEFT(Name, 1) between 'F' and 'P'; Regards, m. -Original Message- From: Dave M G [mailto:d...@articlass.org] Sent: 04 November 2009 14:52 To: mysql@lists.mysql.com Subject: Alphabetical search to and from MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mis...@poczta.fm -- Konkurs: Wygrywaj nagrody z Pudzianem! Sprawd� http://link.interia.pl/f240e -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Alphabetical search to and from
Hi, You're right on one hand. But on the another in some languages you have a more characters after the Z letter. Apart from that try this: select 'F' as test union select 'FAA' order by test; and you'll see: test - F FAA Regards, m. -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: 04 November 2009 15:19 To: Dave M G Cc: mysql@lists.mysql.com Subject: Re: Alphabetical search to and from Haven't tried this myself, but would this not work while permitting the index to engage? select * from country Name between 'FAA' and 'PZZ' On Wed, Nov 4, 2009 at 10:15 AM, misiaQ mis...@poczta.fm wrote: Try this: select * from country where LEFT(Name, 1) between 'F' and 'P'; Regards, m. -Original Message- From: Dave M G [mailto:d...@articlass.org] Sent: 04 November 2009 14:52 To: mysql@lists.mysql.com Subject: Alphabetical search to and from MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mis...@poczta.fm -- Konkurs: Wygrywaj nagrody z Pudzianem! Sprawd¼ http://link.interia.pl/f240e -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mis...@poczta.fm Tanie rozmowy telefoniczne! Sprawdz http://link.interia.pl/f2410 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Alphabetical search to and from
Or : alter table users add first_f_name char(1) not null; create index first_f_name_idx on users (first_f_name); update users set first_f_name = left(first_name,1); ... and don't forget the trigger on insert, update to keep that new column always up to date. Regards, m. -- Konkurs: Wygrywaj nagrody z Pudzianem! Sprawd¼ http://link.interia.pl/f240e -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
