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

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](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) syntax, it's a beautiful thing! But be aware that if you shift database that this is mysql only — Dale, Aug 01 '13 at 08:30
@Dale but how can i make one line query of three queries? including SELECT statement.. — Frank11, Aug 01 '13 at 08: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, Aug 01 '13 at 08:36
@Dale there is 'EXISTS' thing in subquery of sql but i don't know how to use it.. — Frank11, Aug 01 '13 at 08:39

score 1 · Answer 1 · answered Aug 01 '13 at 08:30

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 01 '13 at 08:30

Elon Than

8,984
4
22
37

score 1 · Accepted Answer · answered Aug 01 '13 at 08:31

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 01 '13 at 08:31

DevZer0

13,069
5
24
48

but how to combine 'select' statement with other queries – Frank11 Aug 01 '13 at 08:34
Is your select only to check if the record is there right? – DevZer0 Aug 01 '13 at 08:36
yes... should i use 'EXISTS' thing of subquery of sql – Frank11 Aug 01 '13 at 08:38
so you don't need to use the select in this case the `ON DUPLICATE KEY UPDATE` takes care of that for you – DevZer0 Aug 01 '13 at 08:39
2

Of course you have to create unique index with this two fields (QId and UnqId). – Elon Than Aug 01 '13 at 08:41
@ElonThan i want to include it with SELECT statement. – Frank11 Aug 01 '13 at 08:45
@DevZer0 what should i do when i have serialize data in my duplicate check values in statement LIKE this statement == > `code` " INSERT INTO vwr1_wp_theme_config (`theme_name`, `theme_key` , `theme_value`) VALUES ('themename','widget_contactus_widget','a:2:{i:3;a:2:{s:5:"title";s:0:"";s:8:"template";s:41:"page-templates/side-widget-contact-us.php";}s:12:"_multiwidget";i:1;}') ON DUPLICATE KEY UPDATE theme_value=VALUES(theme_value) "`code` – Chirag Pipariya Jun 18 '16 at 07:43

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

2 Answers2