[RESOLVED] Insert or Update dilemna

[jollyollie]

I have a large database that is updated daily. I am trying to pass a subset of data from one table to another within the database. It's a real estate site and I am trying to create a smaller table to work with houses from a certain office from within all the data.

I can do an insert:

$query = "INSERT INTO table2 SELECT * FROM table1 WHERE officeid = 'xxx'";

But what I want to do is set it so that my script evaluates each record, checks whether it exists in table2, if it does, it updates it, if it doesn't, it inserts it. Is there a way to do this with if ... else or am I just thinking wishfully.

I would be delighted to read up on this if someone could help me figure out the right Google query, I've tried but to no avail.

[jara06]

guess this should be in the database forum instead ...

[Kudose]

Assuming that both tables have a unique ID that are connected in some way, then you should be able to use this.

$sql = "INSERT INTO table1 (SELECT * FROM table2) WHERE table1.officeid!=table2.officeid";
$query = mysql_query($sql);
if($query)
  echo 'Table migration was successful.';
else
 echo 'Table migration failed.';

[jollyollie]

Ok, I have it working using the INSERT IGNORE command.

$query = "INSERT IGNORE INTO table1 SELECT * FROM table2 WHERE table2.officeid='xxx'";