INSERT SELECT with condition

Question

Hello,

I'm trying to do a INSERT SELECT with condition but smthg seems to be wrong. I get an 1064 error for wrong syntax.

Here is the query :

INSERT INTO  `db1`.`table`.`field` (

SELECT a.`field1` , a.`field2` 
FROM  `db2`.`table1` a,  `db2`.`table2` b
WHERE a.`field1` = b.`field1` 
AND b.`field2` =  'value'
)

WHERE a.`field1` =  `db1`.`table1`.`field1`

Thank in advance for any suggestions

Answer 1

Syntax for the insert select is :

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

so in you case it like

  INSERT INTO  `db1`.`table` (.... field list ...)
  select 
    ... col for select .....
  from table 
  where ... where condition ....  

Answer 2

The syntax for INSERT INTO is usually

INSERT INTO myTable (field1,field2,...,fieldN)
SELECT field1,field2,etc FROM myTable WHERE condition

You need to simplify your queries and think through what you're trying to do.

Answer 3

My question was bad. In this case it is not Insert but an Update query which is needed