Data import problem with duplicate datasets
Hello, we have two tables associated with a foreign key constraint. Table A with the primary key and table B with an on delete cascade constraint. We want to delete datasets in Table B if the related dataset in Table A is deleted - that works. Now the Problem: There is a weekly import defined on table A using a load data infile FILE replace into table A command. The problem is the implicit delete/insert from the replace keyword. Therefore the sets in Table B are deleted. We are using replace because some datasets have to be modified in some columns in table A. Is there a possibility to avoid the delete in Table B by importing data in Table A? Regards, Spiker -- Neu: GMX Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02 -- 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 import problem with duplicate datasets
A very first thought I got is disable the constraint before import and re-enable after that. One way could be to set the foreign key checks to false or alter the constraint and remove the 'cascade delete' part. It's just a quick brain storm, please verify the goodness of it, I still need to get my coffee this morning! Claudio 2009/9/23 spikerl...@gmx.de Hello, we have two tables associated with a foreign key constraint. Table A with the primary key and table B with an on delete cascade constraint. We want to delete datasets in Table B if the related dataset in Table A is deleted - that works. Now the Problem: There is a weekly import defined on table A using a load data infile FILE replace into table A command. The problem is the implicit delete/insert from the replace keyword. Therefore the sets in Table B are deleted. We are using replace because some datasets have to be modified in some columns in table A. Is there a possibility to avoid the delete in Table B by importing data in Table A? Regards, Spiker -- Neu: GMX Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
MySQL University session on Sep 24: Concurrency Control: How It Really Works
Concurrency Control: How It Really Works http://forge.mysql.com/wiki/Concurrency_Control:_How_It_Really_Works This Thursday (September 24th, 14:00 UTC (yes, 1 h later than usually)), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on the InnoDB implementation, many of the concepts presented apply to other database systems and storage engines. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks: * October 1: InnoDB Internals: InnoDB File Formats and Source Code Structure (Calvin Sun) * October 8: Building MySQL Releases on Unix (Jörg Brühe) * October 15: The Spider Storage Engine (Giuseppe Maxia) * November 12: Gearman for MySQL (Giuseppe Maxia) The schedule is not engraved in stone at this point. Please visit http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the up-to-date list. On that page, you can also find the starting time for many time zones. Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Retrieving info from 2 tbls ordering it
Hi I have 2tables. 1 for incoming the other for outgoing messages. They both have columns for the userid datetime_received/sent. I'd like to retrieve all records from both tables for a specific user id order all the records returned by the two datetime_received/sent fields. Is this possible if so could someone help me out as to how I could achieve this. Kind regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieving info from 2 tbls ordering it
I think that you can do what you want with a simple union query: select * from incoming where user_id = 123 union select * from outgoing where user_id = 123 order by datetime_received, datetime_sent hth, Arthur On Wed, Sep 23, 2009 at 10:02 AM, Warren Windvogel war...@fontera.comwrote: Hi I have 2tables. 1 for incoming the other for outgoing messages. They both have columns for the userid datetime_received/sent. I'd like to retrieve all records from both tables for a specific user id order all the records returned by the two datetime_received/sent fields. Is this possible if so could someone help me out as to how I could achieve this. Kind regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- Semi-retired SQL guru, interested in interesting projects not YAFOES (yet another friendly order entry system).
RE: Retrieving info from 2 tbls ordering it
You should drop the 'datetime_sent' off that order by clause, it will return an error. Should be select * from incoming where user_id = 123 union select * from outgoing where user_id = 123 order by datetime_received; Always use the column names in the first select for ordering union queries. John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Arthur Fuller [mailto:fuller.art...@gmail.com] Sent: 23 September 2009 15:42 To: mysql@lists.mysql.com Subject: Re: Retrieving info from 2 tbls ordering it I think that you can do what you want with a simple union query: select * from incoming where user_id = 123 union select * from outgoing where user_id = 123 order by datetime_received, datetime_sent hth, Arthur On Wed, Sep 23, 2009 at 10:02 AM, Warren Windvogel war...@fontera.comwrote: Hi I have 2tables. 1 for incoming the other for outgoing messages. They both have columns for the userid datetime_received/sent. I'd like to retrieve all records from both tables for a specific user id order all the records returned by the two datetime_received/sent fields. Is this possible if so could someone help me out as to how I could achieve this. Kind regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com -- Semi-retired SQL guru, interested in interesting projects not YAFOES (yet another friendly order entry system). No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.112/2390 - Release Date: 09/23/09 05:52:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
_Present
http://cfi.iflywestwind.com/nEACWal3iJ.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
