RE: How to remove the duplicate values in my table!
I have always used this for de-duplicating... ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( myField1, myField1 ) ; It works a treat, hope it helps Roger -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 20 November 2008 00:35 To: jean claude babin Cc: mysql@lists.mysql.com Subject: Re: How to remove the duplicate values in my table! On Nov 19, 2008, at 3:24 AM, jean claude babin wrote: Hi, I found the bug in my servlet ,when I run my application it enter one record to the database without duplicate values.Now I want to clean my table by removing all duplicate rows .Any thoughts? I assume you have a unique record identifier like and auto_increment field? If you not, add and auto_increment field, you have to have a unique ID. Assuming the deviceId field is what indicates a duplicate: SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY deviceId HAVING c1 That will give you the highest unique Id of each duplicate, which is what you want to delete assuming you want to keep the first record. If you want to keep the latest, change it to min. Then you want to join on that select so you can use it as your delete filter. DELETE table FROM table JOIN ( SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY deviceId HAVING c1 ) as dupSet ON dupSet.maxUid=table.uniqueId That will delete one duplicate record for each duplicate group at a time. So if you have 10 of the same duplicate, you need to run the query 9 times. It wouldn't be too hard to add another subquery (i.e. LEFT JOIN on the dup select WHERE table.uniqueId IS NULL) to that to filter so you can delete all duplicates in 1 shot. This has always been something I had to do very infrequently, so I never bothered taking it further. Hope that help! Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.2/1782 - Release Date: 11/19/2008 6:55 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TABLE IF EXISTS - doesnt??
I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me?
RE: DROP TABLE IF EXISTS - doesnt??
I am getting this problem when I am calling this from within a stored procedure and from the command line area but from MySQLQueryBrowser Windows package. I have tried the DROP TABLE IF EXISTS from the mysql DOS-type command line and it doesn't error - I do notice that (also in your example) that there is a Warning provided. Maybe what I am seeing in the Query Browser area is in fact a Warning message and not an error!! If so, sorry to have wasted anyone's time Regards Roger -Original Message- From: Fish Kungfu [mailto:[EMAIL PROTECTED] Sent: 15 August 2008 12:43 To: mysql@lists.mysql.com Subject: Re: DROP TABLE IF EXISTS - doesnt?? Hmmm. It works okay for me, without an error when the tabel doesn't exist. I'm using mysql Server version 5.0.51a-3ubuntu5.1 For example: * mysql use lsldatabase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +---+ | Tables_in_lsldatabase | +---+ | lslstore | +---+ 1 row in set (0.00 sec) mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) * As you see, the table recipes doesn't already exist, and I don't get an error. roger.maynard wrote: I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me? -- 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: Auto Fill blank Rows
Now I come to exapnd the requirement it not correct... to elaborate.. I create tables as such... DROP TABLE IF EXISTS filler; CREATE TABLE filler (row_id int); INSERT INTO filler (row_id) VALUES (1); INSERT INTO filler (row_id) VALUES (2); INSERT INTO filler (row_id) VALUES (3); INSERT INTO filler (row_id) VALUES (4); INSERT INTO filler (row_id) VALUES (5); INSERT INTO filler (row_id) VALUES (6); INSERT INTO filler (row_id) VALUES (7); INSERT INTO filler (row_id) VALUES (8); INSERT INTO filler (row_id) VALUES (9); INSERT INTO filler (row_id) VALUES (10); DROP TABLE IF EXISTS original; CREATE TABLE original (row_id int,reference varchar(15)); INSERT INTO original (row_id,reference) VALUES (1,Reference 1); INSERT INTO original (row_id,reference) VALUES (3,Reference 3); INSERT INTO original (row_id,reference) VALUES (9,Reference 9); select filler.row_id,original.reference from filler left join original original on filler.row_id = original.row_id; Which correctly gives: row_id | reference 1 | Reference 1 2 | null 3 | Reference 3 4 | null 5 | null 6 | null 7 | null 8 | null 9 | Reference 9 10 | null === I now need to add several documents each containing up to 10 lines some of which may be blank So.. DROP TABLE IF EXISTS original; CREATE TABLE original (doc_id, row_id int,reference varchar(15)); INSERT INTO original (doc_id,row_id,reference) VALUES (1,1,Reference 1); INSERT INTO original (doc_id,row_id,reference) VALUES (1,3,Reference 3); INSERT INTO original (doc_id,row_id,reference) VALUES (1,9,Reference 9); INSERT INTO original (doc_id,row_id,reference) VALUES (2,2,Reference 2); INSERT INTO original (doc_id,row_id,reference) VALUES (2,3,Reference 3); INSERT INTO original (doc_id,row_id,reference) VALUES (2,7,Reference 7); I now need to get a result like... 1 | 1 | Reference 1 1 | 2 | null 1 | 3 | Reference 3 1 | 4 | null 1 | 5 | null 1 | 6 | null 1 | 7 | null 1 | 8 | null 1 | 9 | Reference 9 1 |10 | null 2 | 1 | Reference 1 2 | 2 | Reference 2 2 | 3 | null 2 | 4 | null 2 | 5 | null 2 | 6 | null 2 | 7 | Reference 7 2 | 8 | null 2 | 9 | null 2 |10 | null == Any thoughts Roger From: roger.maynard Sent: 12 March 2008 17:18 To: Phil Cc: mysql@lists.mysql.com Subject: RE: Auto Fill blank Rows Yup! That's the kind of thing I was looking for - I just had a complete blank moment Thanks!!! From: Phil [mailto:[EMAIL PROTECTED] Sent: 12 March 2008 17:05 To: roger.maynard Cc: mysql@lists.mysql.com Subject: Re: Auto Fill blank Rows you could do something like select dummy.row_id,real.reference from dummy left join real on real.row_id=dummy.row_id; would give NULL on the 'missing' rows, On Wed, Mar 12, 2008 at 12:50 PM, roger.maynard [EMAIL PROTECTED] wrote: Anyone got any bright ideas of how to solve this one? I have documents which can contain up to 15 rows of information. Each row as it is added to the document reference can have any ROW_ID from 1 to 15 when it is added. I want to be able to Auto Fill the blank rows on a SELECT. eg data in table is ROW_ID|Reference === 1 |Reference Line 1 3 |Reference Line 3 9 |Reference Line 9 11|Reference Line 11 15|Reference Line 15 RESULT REQUIRED is ROW_ID|Reference === 1 |Reference Line 1 2 | 3 |Reference Line 3 4 | 5 | 6 | 7 | 8 | 9 |Reference Line 9 10| 11|Reference Line 11 12| 13| 14| 15|Reference Line 15 I've been playing about with joins on a dummy table containing just rows 1 to 15, but am stuck in my thinking at the moment. Any help gratefully received Roger -- Help build our city at http://free-dc.myminicity.com !
Auto Fill blank Rows
Anyone got any bright ideas of how to solve this one? I have documents which can contain up to 15 rows of information. Each row as it is added to the document reference can have any ROW_ID from 1 to 15 when it is added. I want to be able to Auto Fill the blank rows on a SELECT. eg data in table is ROW_ID|Reference === 1 |Reference Line 1 3 |Reference Line 3 9 |Reference Line 9 11|Reference Line 11 15|Reference Line 15 RESULT REQUIRED is ROW_ID|Reference === 1 |Reference Line 1 2 | 3 |Reference Line 3 4 | 5 | 6 | 7 | 8 | 9 |Reference Line 9 10| 11|Reference Line 11 12| 13| 14| 15|Reference Line 15 I've been playing about with joins on a dummy table containing just rows 1 to 15, but am stuck in my thinking at the moment. Any help gratefully received Roger
RE: Auto Fill blank Rows
Yup! That's the kind of thing I was looking for - I just had a complete blank moment Thanks!!! From: Phil [mailto:[EMAIL PROTECTED] Sent: 12 March 2008 17:05 To: roger.maynard Cc: mysql@lists.mysql.com Subject: Re: Auto Fill blank Rows you could do something like select dummy.row_id,real.reference from dummy left join real on real.row_id=dummy.row_id; would give NULL on the 'missing' rows, On Wed, Mar 12, 2008 at 12:50 PM, roger.maynard [EMAIL PROTECTED] wrote: Anyone got any bright ideas of how to solve this one? I have documents which can contain up to 15 rows of information. Each row as it is added to the document reference can have any ROW_ID from 1 to 15 when it is added. I want to be able to Auto Fill the blank rows on a SELECT. eg data in table is ROW_ID|Reference === 1 |Reference Line 1 3 |Reference Line 3 9 |Reference Line 9 11|Reference Line 11 15|Reference Line 15 RESULT REQUIRED is ROW_ID|Reference === 1 |Reference Line 1 2 | 3 |Reference Line 3 4 | 5 | 6 | 7 | 8 | 9 |Reference Line 9 10| 11|Reference Line 11 12| 13| 14| 15|Reference Line 15 I've been playing about with joins on a dummy table containing just rows 1 to 15, but am stuck in my thinking at the moment. Any help gratefully received Roger -- Help build our city at http://free-dc.myminicity.com !
RE: Im being dumb!
Thanks guys!! gone to chase some of Dan's coffee Brain gone.. and too many remnants of FoxPro SQL with INNER JOINS Rog -Original Message- From: Dan Rogart [mailto:[EMAIL PROTECTED] Sent: 06 March 2008 13:53 To: Dan Rogart; roger.maynard; mysql list Subject: Re: Im being dumb! Ack, listen to Nanni not me. Join order doesn't matter, now that I tested some more :). Off to drink more coffee, Dan On 3/6/08 8:45 AM, Dan Rogart [EMAIL PROTECTED] wrote: Hi, On 3/6/08 8:33 AM, roger.maynard [EMAIL PROTECTED] wrote: I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this? You have to do your joins in a chain: A joins to B, B joins to C, C joins to D, and so on. Here's how I made it work in a simple example: mysql create table a (id int, desc1 varchar(255)); Query OK, 0 rows affected (0.13 sec) mysql create table b (id int, desc2 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql create table c (id int, desc3 varchar(255)); Query OK, 0 rows affected (0.07 sec) mysql create table d (id int, desc4 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql insert into a values (1, 'foo'); Query OK, 1 row affected (0.13 sec) mysql insert into b values (1, 'bar'); Query OK, 1 row affected (0.00 sec) mysql insert into c values (1, 'fu'); Query OK, 1 row affected (0.00 sec) mysql insert into d values (1, 'br'); Query OK, 1 row affected (0.00 sec) mysql select a.id,a.desc1,b.desc2,c.desc3,d.desc4 from a - join b on a.id = b.id - join c on b.id = c.id - join d on c.id = d.id; +--+---+---+---+---+ | id | desc1 | desc2 | desc3 | desc4 | +--+---+---+---+---+ |1 | foo | bar | fu| br| +--+---+---+---+---+ Hope that helps, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Im being dumb!
I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this?
Help with a pivot-type issue
This may take a bit of explaining! I have a incoming table structure of PartRef AttribValue ABC0011 10.00 ABC0012 4 ABC0013 A ABC0021 12.00 ABC0022 6 ABC0023 B Etc Where Attrib 1 represents Length Attrib 2 represents Set Qty and Attrib 3 represents Head Shape My Query of SELECT PartRef GROUP_CONCAT(IF(attr_id=1, value,null)) AS Length, GROUP_CONCAT(IF(attr_id=1, value,null)) AS SetQty, GROUP_CONCAT(IF(attr_id=1, value,null)) AS HeadShape From myTable GROUP BY part_ref Gives me PartNo SetQty Length HeadShape ABC0014 10.00A ABC0026 12.00B Which is fine for part ref with only ONE entry... however The data CAN come with multiple entries for each PartRef which now gives me, Correctly PartNo SetQty LengthHeadShape ABC0014,5 10.00,12.00 A,B ABC002612.00B What I now need to do is to SPLIT out the doubled up fields and end up with .. PartNo SetQty LengthHeadShape ABC0014 10.00A ABC0015 12.00B ABC002612.00B Any pointers would be gratefully received Roger
RE: sql help: delete row where only related to one other row
Take a look at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.ht ml CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 20 February 2008 12:44 To: mysql@lists.mysql.com Subject: sql help: delete row where only related to one other row Say I have two tables: table_a -- a_id (primary key) b_id table_b -- b_id (primary key) name there is a one to many mapping between rows in table b and rows in table a. Say I had an Id of a row in table a an (a_id, say 5). Now, what I want to do is delete the row in table_a (easy enough), but I also want to delete the related row in table_b, if it is ONLY related to the a_id of 5. In other words, I want to delete the row from table b, but I don't want to delete a row from table b that is in use by another row in table a. I'm thinking some type of subquery could do this, but I'm not sure. Can some one tell me how to do this? -- 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: select with table name
Can you just do SELECT t1.*, t1 as tablename FROM t1 UNION SELECT t2.*, t2 as tablename FROM t2 Roger -Original Message- From: Miguel Vaz [mailto:[EMAIL PROTECTED] Sent: 17 February 2008 00:04 To: mysql@lists.mysql.com Subject: select with table name Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? What i need is, on the big resulting list, to know from which table the row came from. Example: Table: t1 --- id name --- 1 john 2 mary Table: t2 --- id name --- 1 paul 2 peter I need these results: id namefromtable 1 johnt1 2 maryt1 3 pault2 4 peter t2 Is this possible? If so, how? Thanks! Pag -- 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: mysql to mysql conversion tool
Check out DBConvert variants.. http://www.dbconvert.com/ -Original Message- From: Sharique uddin Ahmed Farooqui [mailto:[EMAIL PROTECTED] Sent: 29 January 2008 18:16 To: mysql@lists.mysql.com Subject: mysql to mysql conversion tool Hi, I'm looking an application which let me convert one mysql to another db. I need this to port my website from one cms (Vivvo) to another (Drupal). -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and I are the initiator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pass Reference to source table in Stored Procedure - How??
Hi Is there any way I can pass the reference to a source table to be used in a SELECT command within a Stored Procedure Something like this CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20)) BEGIN SELECT * FROM myTable; END $$ This gives error cannot find Table myDB.myTable I cant find how to pass reference in this context. If its not possible, are there any suggestions as to how to create a flexible SP whereby I can pass the source? I can do it by repeating the main select within a switch case structure but it doesn't seem a very elegant solution Thanks for looking Roger Maynard Somerset UK
RE: Pass Reference to source table in Stored Procedure - How??
Perfect!!
Thanks a million.
-Original Message-
From: Rolando Edwards [mailto:[EMAIL PROTECTED]
Sent: 23 January 2008 15:45
To: roger.maynard; mysql@lists.mysql.com
Subject: RE: Pass Reference to source table in Stored Procedure - How??
What you need is Dynamic SQL via the PREPARE statement
Like This :
CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20))
BEGIN
DECLARE SQLCommand VARCHAR(1024);
SET SQLCommand = CONCAT('SELECT * FROM ',myTable);
SET @SQLStmt = SQLCommand;
PREPARE s1 FROM @SQLStmt;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END $$
Give it a Try !!!
-Original Message-
From: roger.maynard [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 23, 2008 6:44 AM
To: mysql@lists.mysql.com
Subject: Pass Reference to source table in Stored Procedure - How??
Hi
Is there any way I can pass the reference to a source table to be used
in a SELECT command within a Stored Procedure
Something like this
CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20))
BEGIN
SELECT * FROM myTable;
END $$
This gives error cannot find Table myDB.myTable
I cant find how to pass reference in this context. If its not possible,
are there any suggestions as to how to create a flexible SP whereby I
can pass the source?
I can do it by repeating the main select within a switch case structure
but it doesn't seem a very elegant solution
Thanks for looking
Roger Maynard
Somerset UK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT WHERE NOT EXISTS syntax
Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger
RE: INSERT WHERE NOT EXISTS syntax
I think I sorted it out ... INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS ( SELECT comment_no FROM master_comments WHERE mComments.comment_no = master_comments.comment_no ); Hope this helps someone else . -Original Message- From: roger.maynard [mailto:[EMAIL PROTECTED] Sent: 23 January 2008 18:58 To: mysql@lists.mysql.com Subject: INSERT WHERE NOT EXISTS syntax Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
