 |
MySQLTalk.org
MYSQL discussions groups
|
| View previous topic :: View next topic |
| Author |
Message |
Djellil
Guest
|
 Posted: Sat Dec 03, 2005 12:43 pm Post subject: INSERT RECORD IF NOT EXISTS OR UPDATE |
 |
|
Dear MySQL developers and users,
I'm replicating data from db Anywhere to MySQL. So I wish to know how I can
write this code in MySQL (does MySQL have a way to INSERT
a new record IF one doesn't exist (not INSERT IGNORE, ON DUPLICATE KEY
etc.).
My source in T-SQL (db Anywhere):
____________________________________________________________________________
______________________
IF NOT EXISTS (SELECT 1 FROM tab WHERE colcode = 'xxx') THEN
INSERT INTO tab (colcode, colname, colversion) VALUES ('tr1', 'MyTree',
'version: 1')
ELSE
UPDATE tab SET colname = 'MyTree', colversion = 'version: 1' WHERE colcode =
'tr1'
END IF
____________________________________________________________________________
______________________
I'm looking forward to hearing from you. Thanks!
Petr Dolansky
|
|
| Back to top |
|
 |
Kristen G. Thorson
Guest
|
| Posted: Mon Dec 05, 2005 5:23 pm Post subject: Re: INSERT RECORD IF NOT EXISTS OR UPDATE |
|
|
Djellil wrote:
| Quote: | Dear MySQL developers and users,
I'm replicating data from db Anywhere to MySQL. So I wish to know how I can
write this code in MySQL (does MySQL have a way to INSERT
a new record IF one doesn't exist (not INSERT IGNORE, ON DUPLICATE KEY
etc.).
My source in T-SQL (db Anywhere):
____________________________________________________________________________
______________________
IF NOT EXISTS (SELECT 1 FROM tab WHERE colcode = 'xxx') THEN
INSERT INTO tab (colcode, colname, colversion) VALUES ('tr1', 'MyTree',
'version: 1')
ELSE
UPDATE tab SET colname = 'MyTree', colversion = 'version: 1' WHERE colcode =
'tr1'
END IF
____________________________________________________________________________
______________________
I'm looking forward to hearing from you. Thanks!
Petr Dolansky
|
I believe INSERT IGNORE does do what you wish. Normally, trying to
insert a duplicate record will cause an error and the record will not be
inserted (and the whole statement will be aborted). The IGNORE
statement suppresses only the error. The record is still not inserted
if it is a duplicate.
http://dev.mysql.com/doc/refman/5.0/en/insert.html
"If you use the |IGNORE| keyword in an |INSERT| statement, errors that
occur while executing the statement are treated as warnings instead. For
example, without |IGNORE|, a row that duplicates an existing |UNIQUE|
index or |PRIMARY KEY| value in the table causes a duplicate-key error
and the statement is aborted. With |IGNORE|, the row is still not
inserted, but no error is issued. Data conversions that would trigger
errors abort the statement if |IGNORE| is not specified. With |IGNORE|,
invalid values are adjusted to the closest values and inserted; warnings
are produced but the statement does not abort. You can determine with
the |mysql_info()| C API function how many rows were actually inserted
into the table."
kgt
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
