Re: If it Exists... Update. If it DNE... insert.

You should probably be using REPLACE, not UPDATE/INSERT. REPLACE is covered in the manual, of course, but the basic purpose of it is to replace (!) the data in a table which would otherwise collide with what you are trying to INSERT, based on your PRIMARY KEY.

If you know all the columns for a particular row (based on PRIMARY KEY) and don't care about the existing values, REPLACE is the best option. See my example below. However if you would want to perform calculations based on the existing columns, (e.g. UPDATE t1 SET age=age+1 WHERE name="Jeff"), you need to do an UPDATE, as REPLACE will not do that.

• jsw

mysql> CREATE TEMPORARY TABLE t1 (name CHAR(16) NOT NULL PRIMARY KEY, age TINYINT UNSIGNED NOT NULL); Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ("Jeff", 19); Query OK, 1 row affected (0.02 sec)

mysql> REPLACE INTO t1 VALUES ("Sara", 21), ("Jeff", 20); Query OK, 3 rows affected (0.02 sec)
Records: 2 Duplicates: 1 Warnings: 0

mysql> SELECT * FROM t1;
+------+-----+
| name | age |
 +------+-----+
| Jeff | 20 |
| Sara | 21 |
+------+-----+
2 rows in set (0.03 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.02 sec)

In article <f9854ab2.0107111601.50cad6f6_at_posting.google.com>, "muirwa" <muirwa_at_hotmail.com> wrote:

> 1. Given a Record, check to see if it exists in the table (SELECT).
>
> 1.1 If it exists, UPDATE record in the table. 1.2 If it doesn't
> exist, INSERT record into table.
>
>
> However, given the simple steps above... For every one insert, there
> are probably 100,000 updates. So, essentially there are 99,999 wasted
> "SELECT" queries. But, as far as I can see it, it must be done in order
> to determine whether to INSERT or UPDATE.
>
> OR
>
> Can I assume an "UPDATE", and then check what is returned (Metadata
> maybe?) (i.e. Rows Matched: 0 Changed: 0 versus Rows Matched: 1
> Changed: 1)
>
> Any suggestions or comments would be appreciated!
Received on Sun Jul 22 2001 - 01:34:58 CEST