MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ravi Kumar. Date:September 21 2006 1:24pm
Subject:RE: Conditional copy of values
View as plain text  
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:gerald_clark@stripped] 
Sent: Thursday, 21 September, 2006 18:30 PM
To: Ravi Kumar.
Cc: mysql@stripped
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/mysql?unsub=1




Thread
Conditional copy of valuesRavi Kumar.21 Sep
  • Re: Conditional copy of valuesGerald L. Clark21 Sep
    • RE: Conditional copy of valuesRavi Kumar.21 Sep
  • Re: Conditional copy of valuesMichael Stassen25 Sep
    • RE: Conditional copy of valuesRavi Kumar.25 Sep