[SOLVED] New Table Update

[SeraphZaphiel]

I'm a complete noob at coding mySQL. I know a little about how the database and tables work, but modifications is a little out of my league. So I've done some research and work and this is what I have

 

Server version: MySQL 4.1.11-nt

 

 

Both tables, before modifications

 

page_hits

+-----------+---------+------+

| pagehitID  | groupID  | hits  |

|    1        |  100    |  52    |

|    2        |  101    |  85    |

|    3        |  102    |  23    |

+-----------+---------+------+

 

hits_report

+---------+------+---------+

| groupID  | hits  | oldHits  |

|  100    |  43    |    32      |

|  101    |  57    |    48      |

+---------+------+---------+

 

page_hits is updated live based off of the hits to that web page. hits_report would be updated on a weekly basis.

 

When updated, hits_report.hits would go to hits_report.oldHits

page_hits.hits would go to hits_report.hits

Both of those would match up the groupID

 

However, if there is a new groupID in the page_hits table that isn't in the hits_report, it would be added to the hits_report table, and given an oldHits value of 0.

 

After modifications

 

hits_report

+---------+------+---------+

| groupID  | hits  | oldHits  |

|  100    |  52    |    43      |

|  101    |  85    |    57      |

|  102    |  23    |    0      |

+---------+------+---------+

 

 

So I know at update I'll need something like this:

 

UPDATE hits_report

SET oldHits=hits;

 

UPDATE page_hits, hits_report

SET hits_report.hits=page_hits.hits

WHERE hits_report.groupID=page_hits.groupID;

 

 

 

Now here's where I'm really not sure. Is the easiest way to tell if it's new or not using an IF statement?

something like - IF(page_hits.groupID=hits_report.groupID, [uPDATE etc.],[iNSERT INTO etc.])

 

 

 

Also, when I create the table would it look like this?

CREATE TABLE hits_report (hits_reportID INTEGER AUTO_INCREMENT PRIMARY KEY, groupID INTEGER, hits INTEGER, oldHits INTEGER);

Do I have to have an ID for the hits_report table?

 

 

Hopefully that's pretty clear. Thanks in advance for any help!

[SeraphZaphiel]

Despite the lack of advice I tried to go through with it anyways. I created the table just fine, but it doesn't like my IF statement.

IF page_hits.groupID=hits_report.groupID THEN
UPDATE hits_report
SET oldHits=hits;
UPDATE page_hits, hits_report
SET hits_report.hits=page_hits.hits
WHERE hits_report.groupID=page_hits.groupID;

ELSE
INSERT INTO hits_report VALUES (null,page_hits.groupID,page_hits.hits,"0");

END IF;

 

What is the problem with my syntax? Is there another way to do this without using the IF?

 

Any help is appreciated.

[SeraphZaphiel]

I'm trying

INSERT INTO hits_report ('page_hitID','groupID','hits','oldHits') VALUES (page_hits.page_hitID,page_hits.groupID,page_hits.hits,0) ON DUPLICATE KEY UPDATE hits_report.hits=page_hits.hits

 

But if gives me error:

Unknown table 'page_hits' in field list

 

and gives me an error any time I try to put "FROM page_hits" anywhere.

 

 

PLEASE help me out.

[fenway]

Drop the quotes from the column list.

[SeraphZaphiel]

Thanks for the tip Fenway!

 

So I tried :

INSERT INTO hits_report (page_hitID,groupID,hits,oldHits) VALUES (page_hits.page_hitID,page_hits.groupID,page_hits.hits,0) ON DUPLICATE KEY UPDATE hits_report.hits=page_hits.hits;

 

And got:

Unknown table 'page_hits' in field list

 

 

Also tried:

INSERT INTO hits_report (page_hitID,groupID,hits,oldHits) SELECT (page_hits.page_hitID,page_hits.groupID,page_hits.hits,0) FROM page_hits, hits_report ON DUPLICATE KEY UPDATE hits_report.hits=page_hits.hits;

 

since SELECT actually makes more sense, and received the same error. The table exists, I promise.

[fenway]

To be honest, I've never used it that way... I'd have to check the syntax.

[SeraphZaphiel]

Do you know any other way that I could do what I want to do? I figured I'd need an INSERT somewhere to create new rows, and I figured ON DUPLICATE KEY might be able to just update them.

 

An IF statement makes more sense to me, but I'm used to C++ type programming, so this is somewhat frustrating as I can't get these functions to do what I need them to

[fenway]

Well, I'm confused about the parens around your column list in the select ?

[SeraphZaphiel]

Sorry about the delay, I've been busy with other things.

 

Tried:

 

INSERT INTO hits_report (page_hitID,groupID,hits,oldHits) SELECT page_hits.page_hitID, page_hits.groupID, page_hits.hits, '0' FROM page_hits, hits_report ON DUPLICATE KEY UPDATE hits_report.hits=page_hits.hits;

 

Got the same error.

[gluck]

Try this. Make sure you have unique or whatever kwy constraints

 

INSERT INTO hits_report (page_hitID,groupID,hits,oldHits) SELECT page_hitID, groupID, hits, '0' FROM page_hits ON DUPLICATE KEY UPDATE hits=Values(hits);

[SeraphZaphiel]

Column 'hits' specified twice

 

Not even sure what that means.

[SeraphZaphiel]

Someone else suggested that I needed a JOIN condition, which makes sense. So I tried:

 

INSERT INTO hits_report (page_hitID,groupID,hits,oldHits) SELECT page_hits.page_hitID, page_hits.groupID, page_hits.hits, '0' FROM page_hits INNER JOIN hits_report ON hits_report.page_hitID=page_hits.page_hitID ON DUPLICATE KEY UPDATE hits_report.hits=page_hits.hits;

 

 

And I get:

Unknown table 'page_hits' in field list

 

again I promise the table exists.

[Wildbug]

I tried replicating your situation, and found that although I could construct a one line UPDATE statement, it seems MySQL won't create a new record in a multi-table UPDATE if a record does not currently exist in that table (I could be wrong, but it didn't work for me).  However, you can do this in two or three steps.  First, update the hits report for pages previously existing.  Second, create and populate records for new pages.  The second command uses a LEFT JOIN to identify records which exist in page_hits but do not have a correlate in hits_report.  You can use a LEFT JOIN in an UPDATE statement, but like I said, MySQL doesn't create the new record.

 

UPDATE
page_hits,
hits_report
SET
hits_report.oldHits=hits_report.hits,
hits_report.hits=page_hits.hits
WHERE
page_hits.groupID=hits_report.groupID;

-------------	

INSERT INTO
hits_report
SELECT
page_hits.groupID,
page_hits.hits,
0
FROM
page_hits LEFT JOIN hits_report ON page_hits.groupID = hits_report.groupID
WHERE
hits_report.groupID IS NULL;

 

This worked for me under 5.0.32, but I think it is compatible with 4.1.  The only caveat is that you might need to split the first statement into two statements since the MySQL manual states that "[f]or multiple-table updates, there is no guarantee that assignments are carried out in any particular order."  Since one value is copied to another, then overwritten, according to that quote, it is possible that the commands could be carried out in the "wrong" order.  But that might apply to situations when two seperate tables are updated, and we've only modified two fields in one table....  I think you'll be alright.

[SeraphZaphiel]

Holy crap, it worked!

 

Doing 2 separate commands isn't a problem since I can just save them as a script and run it.

 

You're so awesome! Thanks for ALL of the help.