insert with nested select problem

[faif]

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


[CODE=mysql]
insert into table1 (field1)
(select t2.field2
from table2 t2
where t2.field3="stri ng" and t2.field4=table 1.field4)
[/CODE]

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

Is there a way to achieve this?

Thank you.

[pradeep kaltari]

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?

Thank you.

Hi,
In the above query first the SELECT statement is executed and then the result set is inserted in the TABLE1.

So, it is clear that the "table1" should also be included in the Select statement. The query would look something like:
[code=mysql]
insert into table1 (field1)
(select t2.field2
from table2 t2, table1
where t2.field3="stri ng" and t2.field4=table 1.field4)
[/code]

I hope this solves your problem.

- Pradeep

[mwasif]

Hi faif and pradeep kaltari,
Kindly use appropriate CODE tags when posting source code.

[faif]

Hi,
In the above query first the SELECT statement is executed and then the result set is inserted in the TABLE1.

So, it is clear that the "table1" should also be included in the Select statement. The query would look something like:
[code=mysql]
insert into table1 (field1)
(select t2.field2
from table2 t2, table1
where t2.field3="stri ng" and t2.field4=table 1.field4)
[/code]

I hope this solves your problem.

- Pradeep

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=table 1.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.

[amitpatel66]

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=table 1.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.

Then you need to use UPDATE insted of INSERT.
Try below code:
[code=sql]
UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="stri ng" AND t2.field4=table 1.field4)
[/code]

[faif]

Then you need to use UPDATE insted of INSERT.
Try below code:
[code=sql]
UPDATE table1 SET field1 = (SELECT t2.field2 from table2 t2 WHERE t2.field3="stri ng" AND t2.field4=table 1.field4)
[/code]

Thank you. I tried but mysql doesn't like it... :(
The error message is:

#1064 - You have an error in your SQL syntax near 'SELECT t2.field3
FROM table2 t2
WHERE t2.field' on line 2

Info: The inner query returns multiple rows.

[faif]

Finally, a two-step solution has been found. The solution is:

[code=mysql]
CREATE TEMPORARY TABLE temp
(select t2.field2, t2.field3
from table2 t2
where t2.field3="stri ng");

update table1 t1, temp t
set t1.field1=t.fie ld3
where t1.field2=t.fie ld2;
[/code]

Thank you all for the help.