insert with nested select problem

[faif]

I want to implement a query similar to this in mysql:

insert into table1 (field1)
    (select t2.field2
    from table2 t2
     where t2.field3="string" and t2.field4=table1.field4)

the reference to table1 from the inner query (table1.field4) does not work.

Is there a way to achieve this?

[Xnuiem]

doing it like that creates a circular reference and it cant logically complete since it cant compare something that doesnt exist yet.

Remove the second argument from the where clause and it will work fine.

[faif]

Originally Posted by Xnuiem

doing it like that creates a circular reference and it cant logically complete since it cant compare something that doesnt exist yet.

Remove the second argument from the where clause and it will work fine.

Thank you for the answer, but unfortunately I've already tried this and it is not what I'm looking for.

I want to copy the values of the fields of table2 to the fields of table1. I use the t2.field4=table1.field4 because I want to copy the contents in the appropriate records, and don't create new. When I'm using a copy of table1 in the inner query, new records are created in table1, and this doesn't solve my problem.

[Xnuiem]

You will need to employ a scripting language then. This is the type of thing that spawned PL/SQL in Oracle and the like.

You can use PHP to do it.

The reason SQL doesn't allow that, is due to the rules of data normalization. Replicating the same data in multiple tables is inefficient, and hence SQL as a language doesn't really allow you to get straight to it, you need to go around.

There are reasons to do what you are talking about, especially in a data warehousing environment, but normally in a production database, replication should be kept to a minimum.