[RESOLVED] Synchronizing via INSERT/UPDATE

jollyollie

I've been googling for an answer but am not even sure how to phrase my search on this question. Here's the situation: I have a real estate database with 2 tables. The first is a listing of all houses for sale in the market, updated daily on an automatic basis (via cron.) The other is a listing of just the homes available through a certain agency. I am trying to do an update of the second database that does 2 things, A. Inserts new homes from Table1 into Table2 and B. Updates the existing information from Table2 with current information from Table1 (price changes, etc.)

I have accomplished part A using the following code:

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

I'm having trouble coming up with proper code for the second part. There is additional information added to the rows in Table2 so I know I need to use update to change just the new information but I'm not certain how to use the right variables.

Ok, for my actual question without the background, here it is:

How do I perform an UPDATE of multiple records, matching a certain criteria, but not all records?

Thanks

jollyollie

Here was my initial idea, as well as the error received:

$query = "update table2 set `category1`=`table1.category1`, `category2`=`table1.category2` WHERE `idnumber` = `table1.idnumber`"

The error I get is:

Unknown column 'table1.idnumber' in 'where clause'

I have the field names right, but I'm not sure how else to do this.

Roger_Ramjet

If you are using MySQL version 4.1.0 or higher you can do it with 1 query using the ON DUPLICATE KEY UPDATE clause


"INSERT INTO table2 SELECT * FROM table1 WHERE table1.category='XXXXX'
ON DUPLICATE KEY UPDATE"

You can also specify the columns to update if you so require.

Otherwise, to fix your query, you need to specify table1 in a JOIN

// implicit join
$query = "update table2, table1 set `table2.category1`=`table1.category1`, `table2.category2`=`table1.category2` WHERE `table2.idnumber` = `table1.idnumber`"  

// explicit join - more portable
$query = "update table2 INNER JOIN table1 ON `table2.idnumber` = `table1.idnumber`
set `table2.category1`=`table1.category1`, `table2.category2`=`table1.category2`"

jollyollie

Thank you for your prompt reply! I figured that there was a fairly simple solution that was just beyond my knowledge. I have 2 follow up questions.

All 3 give me error messages. Both of your joins give me the following error:

Unknown column 'table2.category1' in 'field list'

I tried it just using a single field update, changed the field name to see if that was the issue.

The insert gives me the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE' at line 1

How do I specify update fields in the following code?

 "INSERT INTO table2 SELECT * FROM table1 WHERE table1.category='XXXXX' ON DUPLICATE KEY UPDATE"

Just for the record, I'm using PHP version 4.3.10.

Roger_Ramjet

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Basically, specify the column to update and the SELECT column to update it to

INSERT INTO table2
SELECT col1, col2, col3 FROM table1
ON DUPLICATE KEY UPDATE col2=table1.col2, col3=table1.col3

PS syntax error is caused by the `s around the table.column names - I just copy/pasted from yours, sorry.

jollyollie

MySQL Version 4.0.25-standard, if that matters

Roger_Ramjet

jollyollie wrote:
MySQL Version 4.0.25-standard, if that matters

Yes, it matters - needs 4.1.0 as I said. 🙁

In that case you need 2 queries - 1 to update existing rows and 1 to add new rows.

UPDATE table2 INNER JOIN table1 ON table1.idnumber=table2.idnumber
SET table2.col2=table1.col2


INSERT INTO table2
SELECT * FROM table1 WHERE table1.category='XXXXXX'

Run them in that order to save upfdating the new inserts.

jollyollie

Thanks so much for your help! I now have a working setup. Your help was invaluable.

When I looked for version I originally looked at my PHP version rather than my MySQL version, sorry. I'm beginning to get a clue, but I'm not quite there.