if data exists then update else insert, mysql using subquery or etc [duplicate]

Question

'process' is a method for prepared statement
'QId' & 'UnqId' are the child foreign keys (just a index)

i want to make it one line statement (including 3 queries) of mysql using subquery or etc

$get = $call->process("SELECT UnqId FROM table1
                     WHERE QId = ? AND UnqId = ?", 
                     array($_SESSION['Q'], $_SESSION['U']));
if($get) //if table exists
{
$call->process("UPDATE table1 SET col3 = ?, col4 = UTC_TIMESTAMP() 
              WHERE QId = ? AND UnqId = ?", 
                  array('OK', $_SESSION['Q'], $_SESSION['U']));
}
else
{
$call->process("INSERT INTO table1 VALUES (?, ?, ?, UTC_TIMESTAMP(),
               NULL, NULL, NULL, NULL)", 
                  array($_SESSION['Q'], $_SESSION['U'], 'OK'));
}

If you're using mysql you could take a look at INSERT ON DUPLICATE KEY UPDATE syntax, it's a beautiful thing! But be aware that if you shift database that this is mysql only — Dale
– Dale, Commented Aug 1, 2013 at 8:30
@Dale but how can i make one line query of three queries? including SELECT statement.. — Frank11
– Frank11, Commented Aug 1, 2013 at 8:35
I'm not sure if you can, you would run your select first then create a one line insert / update based on the link documentation in my previous comment — Dale
– Dale, Commented Aug 1, 2013 at 8:36
@Dale there is 'EXISTS' thing in subquery of sql but i don't know how to use it.. — Frank11
– Frank11, Commented Aug 1, 2013 at 8:39

DevZer0 · Accepted Answer · 2013-08-01 08:31:22Z

1

You can use a query that uses ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

answered Aug 1, 2013 at 8:31

DevZer0

13.5k7 gold badges29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Frank11 Over a year ago

but how to combine 'select' statement with other queries

DevZer0 Over a year ago

Is your select only to check if the record is there right?

Frank11 Over a year ago

yes... should i use 'EXISTS' thing of subquery of sql

DevZer0 Over a year ago

so you don't need to use the select in this case the ON DUPLICATE KEY UPDATE takes care of that for you

Elon Than Over a year ago

Of course you have to create unique index with this two fields (QId and UnqId).

|

Elon Than · Accepted Answer · 2013-08-01 08:30:49Z

1

Check out ON DUPLICATE KEY UPDATE syntax to do it in one query. And try to search next time, eg. "mysql update on duplicate".

answered Aug 1, 2013 at 8:30

Elon Than

9,8154 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

if data exists then update else insert, mysql using subquery or etc [duplicate]

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related