insert into table is doesn't exist and update if it does?

[cssExp]

I read on one of the threads sitepoint search brought up that I can do it with something like the "ON DUPLICATE KEY" or so but in my table using unique key is not possible.

table `members` has fields `member_name`, `age`, `gender`.

example entry is:

INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female')

However, I want to do the following if 'Tanya' already exists,

UPDATE `members` SET `age`='18', `gender`='female' WHERE `member_name`='Tanya'

.

I can check if it exists via php and do appropriate action, but is there a way to do this through mysql?

[Dan Grossman]

Why is using a unique key not possible? Make the key (member_name, age, gender) and a duplicate insert will fail, allowing you to use the duplicate trigger.

[cssExp]

Why is using a unique key not possible? Make the key (member_name, age, gender) and a duplicate insert will fail, allowing you to use the duplicate trigger.

yes it's possible in this scenario, but on an another table there is a `transactions` with fields `email`, `payment_gateway`, `transaction_id`.

Maybe I should have used this table for asking the doubt, sorry for using the wrong table as example.

example entries can be:

INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'paypal', 't9869978');

INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'alertpay', '6363f78');

However, if tanya@email.com with alertpay exists I want to update entry.

UPDATE `transactions` SET `transaction_id`='6745745yh' WHERE `email`='tanya@email.com' AND `payment_gateway`='alertpay'

Here unique id is not possible is it?

[Dan Grossman]

Sure it is, (email, payment_gateway) is the unique key

Unique constraints don't have to be on a single column

[cssExp]

Sure it is, (email, payment_gateway) is the unique key

Unique constraints don't have to be on a single column

ok if I make those two unique key, then how could there be tanya@email.com for paypal and alertpay? and there will be other entries with paypal or alertpay as well, so making payment_gateway unique key would limit paypal to just 1 entry and alertpay to 1 entry, yes?

suppose an entry also exists: gorge@someemail.com, alertpay, 8798hgh.

umm I guess I'm not able to explain well or maybe I'm totally confused of about what a unique key does

[r937]

the latter

[cssExp]

As far as I am aware, and also confirmed with my brother. If I set payment_gateway to unique then if many entries were to have "alertpay" as payment_gate way - it will reject it as there can be only of same payment_gateway since it's made unique.

If this is true, as per my experience (as little as it may be) I'd say that it may not be that I'm confused about how unique key works.

[r937]

look, the easiest way to discover how this works is to test it --

CREATE TABLE cssExp 
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, foo VARCHAR(99)
, bar VARCHAR(99)
, UNIQUE ( foo,bar )
);
INSERT INTO cssExp ( foo,bar ) VALUES
 ( 'higglety','pigglety' )
,( 'frick','frack' )
,( 'tom','alertpay' )
;

so far so good, yes?

okay, now let's see if you can first predict what's going to happen when you run this --

INSERT INTO cssExp ( foo,bar ) VALUES
 ( 'bob','alertpay' )
;

will this be accepted or rejected?

[cssExp]

yes, it work. so using the following will help me achieve what I want? ok I'll give it a try when I get home. Thanks again.

INSERT INTO `transactions` (`email`, `payment_gateway`, `transaction_id`) VALUES ('tanya@email.com', 'alertpay', '6363f78') ON DUPLICATE KEY UPDATE `transactions` SET `transaction_id`='6745745yh' WHERE `email`='tanya@email.com' AND `payment_gateway`='alertpay'

[r937]

first of all, if you're going to update an existing row, why wouldn't you update the transaction id to the same value as the transaction id that you were trying to insert?

secondly, the ON DUPLICATE KEY UPDATE option does not require a WHERE clause

finally, wouldn't the transaction ids be unique? are we looking at individual transactions that can possibly be repeated?

i wish you would have continued with your original question instead of the "bait and switch" tactic...

table `members` has fields `member_name`, `age`, `gender`

[cssExp]

for your information, I'm trying to learn mysql here - not working for a client or my own projects. So all of these tables here are hypothetical scenarios.

Anyway as I said before, thanks for your guidance. I won't take up any more of your time with this "bait and switch" tactic.. whatever it means.

[r937]

So all of these tables here are hypothetical scenarios.

now it's beginning to sound like a homework assignment

[oddz]

INSERT INTO `members` (`member_name`, `age`, `gender`) VALUES ('Tanya', '18', 'female') ON DUPLICATE KEY UPDATE age = VALUES(age),gender=VALUES(gender)

The column member_name will need to be a primary key or unique key.