Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration & schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id & schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 0:07 + 1/29/06, Jessica Svensson wrote: From: Paul DuBois <[EMAIL PROTECTED]> To: "Jessica Svensson" <[EMAIL PROTECTED]>,mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by "ignore it" you mean "skip the input line and do not load it," you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? LOAD DATA attempts to load every line. The only way it won't happen will be that some error occurs or you're using IGNORE and a duplicate-key error occurs. If you want to selectively ignore lines based on some other criterion, LOAD DATA is probably the wrong approach. At least if you're loading the data directly into the target table. You might consider another approach: Load the data into a temporary table, delete from it those records that have no product_id match (use the multiple-table DELETE syntax that enables you to delete records based on join conditions), and then load the remaining records into your target table (INSERT ... SELECT). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
From: Paul DuBois <[EMAIL PROTECTED]> To: "Jessica Svensson" <[EMAIL PROTECTED]>,mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by "ignore it" you mean "skip the input line and do not load it," you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? _ Nyhet! MSN Messenger i Mobiltelefonen! http://mobile.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by "ignore it" you mean "skip the input line and do not load it," you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA, Ignore in SET?
I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. Thanks in advance. _ Hitta rätt på nätet med MSN Search http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration & schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id & schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration & schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id & schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ignores foreign key relationships between tables?
I have two tables, registration & schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id & schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf files
Hi all. I am having some troubles with what should be contained within my my.cnf file. Would a few of you be willing to show me what yours consists of? Thanks, ~Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied after upgrade to 5.0
Hello. Are you able to connect to MySQL Server if you blank the root password? Use mysql command line client from 5.0 distribution. Have you run mysql_fix_privilege_tables script? Please, provide the CREATE statement for mysql.user table.See: http://dev.mysql.com/doc/refman/5.0/en/access-denied.html M wrote: > Hello, > > I upgraded MySQL from 4.1 to 5.0 on my machine (MDK 2006). Since I can't > connect, I always get: > > ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using > password: NO) > > (or using password:YES) > > I followed the reset password procedure. If the server is started with > --skip-grant-tables, I can see correct entries for user root: > > mysql> select Host, User from user where User = 'root'; > +--+--+ > | Host | User | > +--+--+ > | localhost| root | > | localhost.localdomain| root | > +--+--+ > > encoded passwords are there too. > > Is there anything else I could miss. What should I check, where to look? > > Thank you > > Marek > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to start mysql with --old-password
Hello. > I wanted to know that is it OK to add an entry into /etc/rc.d/rc.local >as /usr/local/mysql/bin/mysqld_safe & . I've seen the systems where mysqld is started in a similar way. However, I usually prefer to work with properly configured mysql.server script (due its ability to do restart of the server in an elegant way :) See: http://dev.mysql.com/doc/refman/5.0/en/automatic-start.html > Also now i cannot write mysqldump without the path . Does it mean that you're unable to invoke mysqldump without specifying the path to it? Check your $PATH environment variable. [EMAIL PROTECTED] wrote: > Hi Gleb Paharenko , > Thanks for replying to the email. > Actually i have done locate my.cnf and cannot find he file. > I am sure this file do not exist on mine VPS. > I wanted to know that is it OK to add an entry into /etc/rc.d/rc.local as > /usr/local/mysql/bin/mysqld_safe & . > Also now i cannot write mysqldump without the path . > I mean has the installation of mysql been awry. > Pl. do help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE behavior
Hello. If you set a column to the value it currently has, MySQL notices this and does not update it. Perhaps it is the answer on your question. See: http://dev.mysql.com/doc/refman/5.0/en/update.html Nicolas Verhaeghe wrote: > Is it normal for MySQL to not update fields that are already identical? > > I am talking about an INNER JOIN UPDATE, when copying from table A over > table B. > > My count did not match at the first run and when I did the second run, I > go a zero rows updated... > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE behavior
Is it normal for MySQL to not update fields that are already identical? I am talking about an INNER JOIN UPDATE, when copying from table A over table B. My count did not match at the first run and when I did the second run, I go a zero rows updated... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Again Doubt on Query pls help (Doubt not cleared)
I think this is what you want to do: SELECT t1.Division,t1.year, t1.units this_year_units, t2.units last_year_units, case when t2.units is null then t1.units else (t1.units-t2.units) end as Difference_Units FROM `narra_table` t1 left join narra_table t2 on (t1.Division=t2.Division) and (t1.Year=t2.Year+1) hope this helps Veerabhadrarao Narra wrote: i have to write one query DivisionUnitsYear a 200 2004 a 300 2005 b 500 2004 b 800 2005 b 900 2006 c 100 2004 From these values i want to retreive as like this DivisionUnitsYear a200 2004 a100 2005 b500 2004 b300 2005 b100 2006 c100 2004 Group By division names and year with difference. Means difference of the Units values by year can u give me this query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
Thank you Gleb, I'm afraid I'm in over my head on this subject as I am restricted to make db changes via sql or phpmyadmin.It seem from your references the database character_set needs to be set to latin? I'm speculating to change character_sets and collations I need access to other utilites, programs or server administrative functions I am not aware of. I have inspected other servers I work on which enable me to insert accented characters and it appears they all have the following settings: character_set: latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis... Am I correct to assume changes to character sets must be done via command line? cw "Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello. > > Chris, the collation is subordinated to the character set. You should > work with the character sets, and only after with collations. The data > which you store in your table is silently converted to ascii character > set. Are you sure that the characters which you want to store are > present in ascii character set? You should change the character set > of the fields of your table to that one which can hold non English > characters. Another question - are you sure that the data which you're > passing to MySQL is in latin1 encoding? See: > http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html > http://dev.mysql.com/doc/refman/5.0/en/charset-general.html > > How to change the character set of the fields is described at: > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html > > > > Chris wrote: >> I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in >> @@sql_mode). >> >> The database has a Collation = ascii_general_ci. The only other option is >> ascii_bin. >> >> With respect to the table, it also has Collation of the same, >> ascii_general_ci. There are many Collation types which the table may be >> change to, including several of the utf8 verity (utf8.bin, >> utf8.danish.ci,) but no utf8 without an extension. I tried setting >> the >> table to utf8.unicode.ci, but still encounter the INSERT error as before. >> > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied after upgrade to 5.0
Hello, I upgraded MySQL from 4.1 to 5.0 on my machine (MDK 2006). Since I can't connect, I always get: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) (or using password:YES) I followed the reset password procedure. If the server is started with --skip-grant-tables, I can see correct entries for user root: mysql> select Host, User from user where User = 'root'; +--+--+ | Host | User | +--+--+ | localhost| root | | localhost.localdomain| root | +--+--+ encoded passwords are there too. Is there anything else I could miss. What should I check, where to look? Thank you Marek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
i have one doubt
i have to write one query DivisionUnitsYear a 200 2004 a 300 2005 b 500 2004 b 800 2005 b 900 2006 c 100 2004 >From these values i want to retreive as like this DivisionUnitsYear a200 2004 a100 2005 b500 2004 b300 2005 b100 2006 c100 2004 Group By division names and year with difference. Means difference of the Units values by year can u give me this query. -- veerabhadrarao narra, +91-988-556-5556 I-ONE TECH LABS Pvt Ltd. HYDERABAD, INDIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Again Doubt on Query pls help (Doubt not cleared)
i have to write one query DivisionUnitsYear a 200 2004 a 300 2005 b 500 2004 b 800 2005 b 900 2006 c 100 2004 >From these values i want to retreive as like this DivisionUnitsYear a200 2004 a100 2005 b500 2004 b300 2005 b100 2006 c100 2004 Group By division names and year with difference. Means difference of the Units values by year can u give me this query. -- veerabhadrarao narra, +91-988-556-5556 I-ONE TECH LABS Pvt Ltd. HYDERABAD, INDIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union/collation problem, error 1267: feature or bug?
Hi Gleb, i finally found out a method to be entirely independent from any character-set as well as collation-sequence problem, when forming a UNION, where you occasionnally have to insert place-holders in one of the SELECT statements: as (text, varchar, char) placeholders use NULL instead of "" or '' if your application permits it. thanks for your suggestion an help. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to start mysql with --old-password
Hi Gleb Paharenko , Thanks for replying to the email. Actually i have done locate my.cnf and cannot find he file. I am sure this file do not exist on mine VPS. I wanted to know that is it OK to add an entry into /etc/rc.d/rc.local as /usr/local/mysql/bin/mysqld_safe & . Also now i cannot write mysqldump without the path . I mean has the installation of mysql been awry. Pl. do help me. -- Regards Abhishek jain Gleb Paharenko wrote .. > Hello. > > See: > http://dev.mysql.com/doc/refman/5.0/en/program-options.html > > I agree, some times it takes some time find the configuration file. > show variables like '%dir%' should help you. > > [EMAIL PROTECTED] wrote: > > Dear Friends, > > I need to start mysql with --old-passwords but i did not know how to > do so. > > Actually i had mysql installed with someone else. I did not know how > have > > he installed that. > > He have placed an entry in /etc/rc.d/rc.local as > > /usr/local/mysql/bin/mysqld_safe & to start mysql when server starts. > > Also i have no file as my.cnf . > > I have 3 ques: > > 1)Is it correct ot add that entry into this file > > 2)Also how to start mysqld with --old-passwords etc. > > 3)How to add a configuration file. > > > > Pl. do help me with the asnwers. > > I shall be very grateful. > > -- > > Regards > > Abhishek jain > > > > > > mail2web - Check your email from the web at > > http://mail2web.com/ . > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ><___/ www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: i have one doubt
Hi Gleb Paharenko, ThanQ for assistance -- veerabhadrarao narra, +91-988-556-5556 I-ONE TECH LABS Pvt Ltd. HYDERABAD, INDIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to start mysql with --old-password
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/program-options.html I agree, some times it takes some time find the configuration file. show variables like '%dir%' should help you. [EMAIL PROTECTED] wrote: > Dear Friends, > I need to start mysql with --old-passwords but i did not know how to do so. > Actually i had mysql installed with someone else. I did not know how have > he installed that. > He have placed an entry in /etc/rc.d/rc.local as > /usr/local/mysql/bin/mysqld_safe & to start mysql when server starts. > Also i have no file as my.cnf . > I have 3 ques: > 1)Is it correct ot add that entry into this file > 2)Also how to start mysqld with --old-passwords etc. > 3)How to add a configuration file. > > Pl. do help me with the asnwers. > I shall be very grateful. > -- > Regards > Abhishek jain > > > mail2web - Check your email from the web at > http://mail2web.com/ . > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: i have one doubt
Hello. set @s:=0; select Division, Units, Year from (select v1.Division, [EMAIL PROTECTED] as Units, v1.Year, @s:=v1.Units from veer v1) as v2; Veerabhadrarao Narra wrote: > i have to write one query > > DivisionUnitsYear > ameerpet 200 2004 > ameerpet 300 2005 > ameerpet 500 2006 > > From these values i want to retreive as like this > > DivisionUnitsYear > ameerpet 200 2004 > ameerpet 100 2005 > ameerpet 200 2006 > Means difference of the Units values by year can u give me this query. > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
Hello. Chris, the collation is subordinated to the character set. You should work with the character sets, and only after with collations. The data which you store in your table is silently converted to ascii character set. Are you sure that the characters which you want to store are present in ascii character set? You should change the character set of the fields of your table to that one which can hold non English characters. Another question - are you sure that the data which you're passing to MySQL is in latin1 encoding? See: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html http://dev.mysql.com/doc/refman/5.0/en/charset-general.html How to change the character set of the fields is described at: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Chris wrote: > I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in > @@sql_mode). > > The database has a Collation = ascii_general_ci. The only other option is > ascii_bin. > > With respect to the table, it also has Collation of the same, > ascii_general_ci. There are many Collation types which the table may be > change to, including several of the utf8 verity (utf8.bin, > utf8.danish.ci,) but no utf8 without an extension. I tried setting the > table to utf8.unicode.ci, but still encounter the INSERT error as before. > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]