How to upsert two database tables with one SQL statement?

There are two database tables described in the following. The asterisk highlights the primary key开发者_如何学Cs.

+----------------+    +----------------+
| posts          |    | url_references |
+----------------+    +----------------+
| id*            |    | url*           |
| post_content   |    | post_id        |
+----------------+    +----------------+

I want to insert or update a post based on the existence of the corresponding entry in the table url_references. What is the best combination of SQL commands to reach this? I would like to avoid to process the decision about insert or update in PHP.

The following scenarios describe the alternative step by step behavior using PHP commands.

SELECT * FROM url_references WHERE url = $url;

Scenario 1: Insert new entry.

// mysql_num_rows() returns 0
INSERT INTO post (post_content) VALUES ($postContent);
$postId = mysql_insert_id();
INSERT INTO url_references (url, post_id) VALUES ($url, $postId);

Scenario 2: Update existing entry.

// mysql_num_rows() returns 1
$row = mysql_fetch_array($rows);
$postId = $row['post_id'];
UPDATE posts SET post_content = $postContent WHERE id = post_id;

Edit 1: Note, that I cannot check for the id in posts directly! I want to manage (insert/update) posts based on their url as the primary key.

If you would like to do above in a classical way, do the following.

First we may agree that url is a natural primary key. Anyway you need an index in this column to speedup your lookups:

CREATE UNIQUE INDEX url_references_idx ON url_references(url);

After that if you execute:

INSERT INTO url_references (url) VALUES ($url);

you end up with two scenarios:

• The INSERT succeeds. That means your $url is new and you can proceed with:

INSERT INTO posts (id, post_content) values (NULL, $postContent);
SELECT LAST_INSERT_ID(); // This will return $post_id
UPDATE url_references SET post_id = $post_id WHERE url = $url;
COMMIT;

In this scenario the lock on newly inserted row in url_references guarantees that another thread will go the 2nd scenario, if 1st transaction is successfully committed (or 1st scenario if it fails).

• The INSERT fails. That means your $url is already known and you can proceed with:

SELECT post_id FROM url_references WHERE url = $url;
UPDATE posts SET post_content = $postContent WHERE id = $post_id;
COMMIT;

Note: The first INSERT statement guarantees that racing condition on url_references table is correctly handled provided you have enabled a correct transaction isolation level and autocommit=off.

Note: Using SELECT ... FOR UPDATE does not work in this case, as it will lock only existing rows (and we need to lock non-existing row, which is about to be inserted). Sorry if I confused you, please ignore my comment under your question.

You can use REPLACE INTO to avoid deciding between INSERT/UPDATE on the posts table, then based on the number of rows affected by REPLACE determine whether you need to INSERT into url_references. Something like this:

$sql = "REPLACE INTO posts (post_id, post_content) VALUES ($postId, $postContent)";
$result = mysql_query($sql);
$numRows = mysql_num_rows($result);

if ($numRows == 1) {
    // was INSERT not DELETE/INSERT (UPDATE) - need to insert into url_references

    $postId = mysql_insert_id();
    $sql = "INSERT INTO url_references (url, post_id) VALUES ($url, $postId)";

    ... do SQL INSERT etc
}

From the MySQL REPLACE documentation:

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted.

Note that I haven't tested the above pseudo-code, so you may have to tweak it.

See: http://dev.mysql.com/doc/refman/5.0/en/replace.html for reference.