Re: Conditional copy of values
Ravi Kumar. wrote: Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' But I want to insert only those rowid values which do not exist in T2. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. You've already got your solution, but, for the record, subqueries ARE supported in mysql 4.1.14. They aren't supported in version prior to 4.1. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Conditional copy of values
True, but they are very slow in 4.1.14. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 26 September, 2006 00:12 AM To: Ravi Kumar. Cc: mysql@lists.mysql.com Subject: Re: Conditional copy of values Ravi Kumar. wrote: Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' But I want to insert only those rowid values which do not exist in T2. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. You've already got your solution, but, for the record, subqueries ARE supported in mysql 4.1.14. They aren't supported in version prior to 4.1. Michael -- 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: Conditional copy of values
Ravi Kumar. wrote: Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. Then what is the reason for table2 ? I see no value for a single element table unless it is used to hold valid entries for another table. You are populating it from Table1, so it now has no reason to exist. Use Table1 instead. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' What is T2 doing in this select ? This is not a join, but a Cartesian product. But I want to insert only those rowid values which do not exist in T2. Use insert ignore. You already have a primary key on rowid, so you cannot add a rowid that already exists. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. Thanks, Ravi. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Conditional copy of values
Dear Gerald, Thanks for the reply. Here is what I was looking for (Google helped me find this): insert into Table2 (rowid) select T1.rowid from Table1 T1 left join Table2 T2 on T1.rowid = T2.rowid where T1.rowid is null and T1.somecolumn = 'somevalue' The 'T1.rowid = null' section prevents duplicate rowid values T2. Thanks again. Ravi. -Original Message- From: Gerald L. Clark [mailto:[EMAIL PROTECTED] Sent: Thursday, 21 September, 2006 18:30 PM To: Ravi Kumar. Cc: mysql@lists.mysql.com Subject: Re: Conditional copy of values Ravi Kumar. wrote: Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. Then what is the reason for table2 ? I see no value for a single element table unless it is used to hold valid entries for another table. You are populating it from Table1, so it now has no reason to exist. Use Table1 instead. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' What is T2 doing in this select ? This is not a join, but a Cartesian product. But I want to insert only those rowid values which do not exist in T2. Use insert ignore. You already have a primary key on rowid, so you cannot add a rowid that already exists. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. Thanks, Ravi. -- Gerald L. Clark Supplier Systems Corporation -- 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]
