How To Insert If A Value Does Not Exist And Select If It Does?
Basically, I am doing a users table with an ID and a name for now.
I want to create a stored function that returns the ID of a user and creates the user if it does not already exist.
The way I do it for now is:
Select the user record by name.
If we had one, return its ID.
If not, insert a new user record and return its ID.
But I am worried about concurrency. What if another thread runs the same function while this one is between steps 2 and 3? TWO user records would be created for the same user. That would be bad.
I added a UNIQUE index to the username, but this means that the stored function will just error in a case like this, instead of returning the already existing user. This is also undesirable.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Insert If Not Exist
when I need insert rows if a field not exist in database I I do like this : $count = mysql_num_rows(mysql_query("select mail from useres where mail ='$mail'")); if ($count < 0) $query = mysql_query("insert into users set mail='$mail'") I mean a code with this algorithm : insert a row to database if one field not inserted at last and not exist ( if exist ignore )
View Replies !
View Related
If Not Exist Update Else Insert
I am trying to make a right syntax but I don't know how. Please help me to correct it:  mysql_query("IF NOT EXISTS (INSERT INTO users (firstName, lastName, id2, counter) VALUES ('Nick', 'AAAA', '4', '$views')) ELSE (UPDATE users SET firstName='Nick', lastName='AAAA', id2='4', counter='$views' WHERE ID='4')") or die (mysql_error()); //or mysql_query("IF EXISTS (UPDATE users SET firstName='Nick', lastName='AAAA', id2='4', counter='$views' WHERE ID='4') ELSE (INSERT INTO users (firstName, lastName, id2, counter) VALUES ('Nick', 'AAAA', '4', '$views'))") or die (mysql_error());Â
View Replies !
View Related
Insert When Entry Is Not Exist
I want to be able to insert the new entry if the old entry not exist. Do i need to do the select then insert? Is there a better way to do it for faster & large database? Code: $query = "select count(*) as itisexist from AA where BB='$var'"; $result = mysql_query($query); $line = mysql_fetch_array($result, MYSQL_ASSOC) if ($line['itisexist'] == 0 ) { $query = "insert into AA set BB='$var'"; mysql_query($query); }
View Replies !
View Related
IF EXIST UPDATE ELSE INSERT
What I have is one table A, with fields ID and COUNT. What I want to do is to update A set COUNT='y' where ID='x'... if ID='x' exist. If it does not exist, then insert into A values ('x', 'y'). I do need to do this in one query, because if I check for existence first and then come back to update/insert... conditions may not be the same as when I did my initial check.
View Replies !
View Related
Insert Values Only If They Do Not Already Exist In Table
I want to insert some values from one table (A) into another table (b). However there are some values in table A which exist two times in table A. I only want to insert values one time. So If a certain field value also exist in some other row (same fieldname) then I don't want to insert it twice. It doesn't matter which of the two rows will be inserted. Can this be done with MySQL? Maybe with some Control Flow Functions?
View Replies !
View Related
Stored Procedures :: IF ID Exist, Update And IF Not Insert New
I know there is a lot of debate about when and if to use them. I'm thinking that I'm going to use them to eliminate muliple connections to the database- for instance: say I have an "ID". I want to query the table and see if that ID exists, if it does not exist, create a new record with that ID, if it does exist, incremement one of the columns in the table for that record. Are these the type of operations that can, and should, be done in one SQL operation to conserve unncecesary multiple connections? ie- instead of querying the database once to see if the ID exists, then using the application language (PHP, etc) to determine whether or not to INSERT or UPDATE, then executing another query Also - How do you unit test & debug MySQL stored procs? I only know how to create a procedure. What if it's not working right and I need to only change one word & rerun? Don't know how to do this...
View Replies !
View Related
Insert Multiple Fails Completely If Any Duplicates Exist
I've been having some problems with database performance... Several threads are constantly attempting INSERTs of new records into a large table - that is hundreds of thousands of records -large by my account :-) The table has a VARCHAR field tagged as UNIQUE and inserts are kind of slow. i'm relying on the INSERT to fail on duplicates, i'm not performing any checking before i attempt the INSERT (i figured an additional SELECT would slow things even more - but any advice to the contrary would be appreciated). While these back end threads are constantly banging away with their INSERTS, front end users are hitting the DB with searches. so i end up with several threads simultaneously attempting very expensive DB operations. the result is that the front end that users see is DREADFULLY slow. My proposed speedup is to modify the behavior of the backend threads so that they don't constantly attempt INSERTS of single entries, but build up a hashtable of local entries and at some interval, attempt a multiple insert something like: INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2).... While i do expect to take a big hit when these multiple inserts launch, in the time between, i expect front end performance to be significantly improved. The problem with this situation is that the multiple insert may contain one or more new entries which will cause duplicates on my table's UNIQUE VARCHAR field. I would like to have duplicates ignored on insert, but have all newly unique entries be added. Sadly, from what i've seen, once a duplicate violation for any of the new entries is found, the call fails, leaving my newly UNIQUE entries NOT ADDED. Is there any way to get the call to work as i want? Doing many single row inserts sounds like it'll be much slower (judging from what i've read) so i'd rather not resort to that. I don't have a ton of DB experience (which may be obvious) so if i've overlooked something simple please let me know. If there's a better way to improve performance other than these multiple inserts, that would be nice to know about too. Thanks for any insight,suggestions, etc...
View Replies !
View Related
Select Where Exist
I have this tables: category (id,name) message (id,text,category_id) I need a query like this : insert into message set text='mytext',cid='$cid' where "cid exist in category" I need some thing like this "cid exist in category"
View Replies !
View Related
SELECT JOIN Where A Record Doesn't Exist
I have two tables. A company table and a table with a list of modules that links to the company table. I have had think about this but not come up with any sort of solutions. How to I select all companys where a company module record doesn't exist. i.e. Company1 module1 module2 Company2 module1 module2 Company3 module1 module2 Company4 module1 module3 So how would I select only Company4 where there is no module2. Please note there is many to one relationship between company and module, i.e many module to 1 company. If I use something like SELECT * FROM company, modulelist WHERE modulelist.module!=module2 it doesn't appear to work.
View Replies !
View Related
Select Values For A Column That Doesn't Exist
have two tables of data: a,b,c and you have a new table that has all the original columns as the first table, plus a new column: a,b,c, d let's say that there is no data that currently exists for column d. Is there a way that I can specify a single value on my select query of the first table (a,b,c) that creates a result set that has a default value and a d column (a,b,c, d=0)?
View Replies !
View Related
How To Select Default Value If The Given Entry Doesn't Exist In Table?
user | data ---------------------- root | data1 mike | data2 default | data3 ---------------------- I need to select some data from the data column for a user, which is very simple: > select data from table where user='mike' Problem: when a user doesn't not exist in the table, the select query has to return data for "default" user (also in the table) and I don't know how to do it. So, it should be something like this: > select data from table where <if john exists user='john'> <else user='default'> in the table above, user 'john' doesn't exist and this query is supposed to return 'data3' value. I've read that "if/then", "if exists" statements are used in procedures only,
View Replies !
View Related
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
View Related
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
View Related
INSERT SELECT
I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT) Thanks again //Lars Rasmussen -----Oprindelig meddelelse----- Fra: Jay Blanchard [mailto:jay.blanchard@niicommunications.com] Sendt: 13. august 2003 19:59 Til: Lars Rasmussen; mysql@lists.mysql.com Emne: RE: INSERT .... SELECT [snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers).
View Replies !
View Related
INSERT... SELECT
Im trying to insert a bunch of rows into a table. If the row already exists id like to update the row 'counter'. For example... INSERT INTO table1 SELECT field1, field2 FROM table2 ON DUPLICATE KEY UPDATE field2 = 1 Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY? I cant get it to work.
View Replies !
View Related
Insert Into Xyz Select * From Abc
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of "abc". Is there any simple way to inject all the rows of "abc" into "xyz"? Tried "insert into xyz select * from abc" but got a complaint of "column count doesn't match value count at row 1", which of course is true. Any way to tell SQL to simply set defaults for the missing columns?
View Replies !
View Related
INSERT And SELECT
I'm wondering if it is possible to run an INSERT...SELECT query where I am grabbing data from Database1 with the SELECT query and inserting this into tables in Database2 with the INSERT.I saw one of the comments over at: http://dev.mysql.com/doc/refman/5.0...ert-select.html (third or fourth comment down) suggested that this was possible, al-be-it this is in the manual for version 5 of MySQL and I am using 4.1.11
View Replies !
View Related
INSERT/SELECT Row
I'm trying to duplicate multiple rows from table_parent into table_child. However, I would like to set the value of column1 normaly. (i.e. insert a static value not retrieved from table_parent) Both column1 and column2 are primary keys in both tables. Column1 needs a different value in table_child from what it has in table_parent. The SQL for a direct duplication would be: PHP Code: mysql_query("INSERT INTO table_child(column1, column2) SELECT column1, column2 FROM table_parent WHERE column1 = $var_column1", $connection); How do I change the values being inputted from table_parent.column1, table_parent.column2 to $var_phpVariable, table_parent.column2? I can do this simply enough with a loop but would rather not spend up to 30+ queries when it could be done in one query.
View Replies !
View Related
Insert Using Select
Can anyone let me know if it possible to formulate an insert query that that can use both select and values, so that I can get certain data from the another table and have fixed values for some of the fields in the insert query? I guess one way would be to do an insert for select statement, then an update for fixed values.
View Replies !
View Related
INSERT With SELECT
I've been working on a stored procedure which takes rows from one table and inserts them into a temporary created memory table, but I've stumbled accross a small roadblock (I hope at least)... Here's the statement I'm trying to exectute INSERT INTO temptable SELECT * FROM template WHERE type_name like '%jack%' ORDER BY RAND() LIMIT 1; if I take out the ORDER BY RAND() it works, but natrually it always selects the first record, is it possible to have the ORDER BY RAND() in that select, or would I need to do it another way...
View Replies !
View Related
Insert Using A Select
Can seomeone tell me what I'm doing wrong? Here's a simplified version of what I'm doing: CREATE TABLE `test1` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; CREATE TABLE `test2` ( `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL, `val3` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; insert into test2 (val1, val2, val3) values ("some", "test", "values"); ******** Then I try to do this, am I doing something wrong or is this not possible? insert into test1 (test1.val1, test1.val2) values (select test2.val1, test2.val2 from test2 where 1);
View Replies !
View Related
Insert Into Select
I've search and search and I haven't found the answer. I am new to MySQL but not new to SQL. I teach an SQL class. This term, we choose to use MySQL. We are trying to execute this: Insert into temp_table select zip from customer temp_table is being created on the fly. We get the following error: Error 1146 (42S02) ...table 'temp_table' doesn't exist.
View Replies !
View Related
Insert And Immediate Select
I add a bunch of records using ExecuteNonQuery() in C# (OdbcCommand). Immediately after the INSERT, I do a SELECT (with a different connection) on the same data. And once in a while (not often, but still...), the records just inserted is not included. If I try again, it works. Each time, "rows affected" return a value greater than 0, so the data should be there somewhere... Is there a way to make sure that records are "firmly" added to the database before I go ahead with the subsequent SELECT (preferably without using transactions)?
View Replies !
View Related
INSERT ... SELECT ... AND
i try to do a subquery on INSERT ... SELECT i need the id of two entrys from one table and want to insert them into a nother table. also i want to insert a value to the same table. this syntax doesnt work... INSERT INTO a (ID1, ID2, C) SELECT lemma.id FROM lemma WHERE lemma.lemma="my" AND SELECT lemma.id FROM lemma WHERE lemma.lemma="question" AND VALUE (4); how can i do this?
View Replies !
View Related
INSERT INTO SELECT
I am new to mysql and would appreciate someone helping me out with this converting this statement from postgresql to mysql. INSERT INTO ossurjoinquestions SELECT *, RANDOM() as random_sort, trunc(1144355460, 0) as instanceid FROM ossurcommonquestions WHERE tid = '3'; So far I have INSERT INTO ossurjoinquestions( tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path, random_sort, instanceid ) SELECT tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path FROM ossurcommonquestions WHERE tid = '3' What I am not sure how to do is add RAND() as random_sort or trunc($var,0) as instanceid.
View Replies !
View Related
Insert Then Select
if I have table with a field named myCounter and i auto increments when I do an insert, what is the best way to get that value. This is what I am doing now. myTime = Now Insert into myTable (Value1, Value2) values (myTime, "myValue") 'then I do this right after to get myCounter Select myCounter from myTable where Value1 = myTime and Value2 = myValue
View Replies !
View Related
Insert Into ... Select
I am looking at creating a simple versioing system for a small CMS I have written. An obvious way to do this would seem to me to do a INSERT INTO .. SELECT whenever the page is amended, but before the amendments are stored. ie copy the data first. But although the structure of the table is virtually the same, I need to add columns to it for the actual versioin number eg. Table A has 'id, title, content, editdate' Table B has 'uniqueid, version,id, title, content, editdate' Though the position of 'version' can actually be anywhere.(uniqueid may, or may not, be required) Can I achieve this in one? and if so how?
View Replies !
View Related
Insert From Select
INSERT INTO thistable (SELECT * FROM thistable WHERE id=Ƈ') The problem is, there is a unique key ID, thus that statement generates an error duplicate key. I know that I can change the sql by listing all the fields, but is there any other way? because if I list all the fields, firstly there are many fields to be type in and secondly if I happened to add/edit some fields later and I forget to change this sql, then it wil produce wrong result.
View Replies !
View Related
INSERT SELECT Not Working
I have some trouble since i've upgraded from 3.23.49 to 4.0.13. I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 I tried IGNORE, but it's just not working. I hope anyone of you got a workaround for this problem, i did'nt find any in the manual.
View Replies !
View Related
INSERT SELECT In One Query
I keep getting an error with this query: Insert into new_voter (salutation, firstname, lastname, electoral_district, poll_number, birthdate, gender, occupation, party_affiliation, ph_home, email) values select (txtSalutation, firstname, lastname, txtElectDistrict, txtVotingArea, dtBirth, txtGender, txtOccupation, nPartyId, txtPhoneDay, email) from person Can someone spot the error (presumably with the syntax).
View Replies !
View Related
INSERT Values From SELECT
This works: INSERT INTO content (group_id) SELECT max(id)+1 FROM content; But how do I enter into more than one field? This doesn't work. INSERT INTO content (group_id, content_id) (SELECT max(id)+1 FROM content), 55; OR INSERT INTO content (group_id, content_id) VALUES ((SELECT max(id)+1 FROM content), 11); You can't specify target table 'content' for update in FROM clause
View Replies !
View Related
Insert...select...error
I am getting this error msg: Subquery returns more than 1 row ("INSERT INTO customerbook (booking_ID,room_Type) values((select booking_ID from bookings where booking_ID = bookings.booking_ID),'$a')") ID is reference from table1... I m tryin to get this output: ID type 1 single 1 ensuite any idea?
View Replies !
View Related
Select From Table Before Insert
I am trying to create a trigger that will select the value for an integer from the table on which the insert is occuring, increment it by one, and insert the value into the new row on the same table. Basically, we have a user referral id. I want to grab the rank of the referral user, increment it, and insert it into the rank of the new user. i cannot figure out how to do this for the life of me. Code:
View Replies !
View Related
Insert With Both Values And Select
I need to insert multiple values in a table I use an insertstatement like this one insert into table(name, jobID) select 'Jon', id from jobs where name = 'programmer'; However, if I have a value before the condition like insert into table (jobID, name) select id from jobs where name='programmer', 'jon2'; it does not work and I need such a statement to work cuz I get different data with a lot of fields and I do not know in advance which will be fields with have direct values and hich will ghave to get data from other tables
View Replies !
View Related
Concurrent Select And Insert
we have a system that encompasses hundreds of nodes. We use a MySQL db to archive messages prouduced by different applications sent via an in-house transport mechanism that follows the producer/server/consumer scheme. As you can imagine the message transmission rate can be fairly large. A PHP based tool can be used to browse the messages stored on the database. An application runs in the MySQL server to get the messages and put them on the db. The problem is that when the messages are browsed with th PHP-based tool, the mysqld SELECT thread blocks the mysqld INSERT threads created by the 'Receiver' application. This, cannot send an acknowledge to the transport mechanism and the conection is broke after a 1 sec. timeout, in other words it does no longer receive messages. My question is then if there is a way of forcing the INSERT opeartion even when a lenghty SELECT is taking place. I'm using MySQL 3.24 and have tried INSERT HIGH_PRIORITY and INSERT DELAYED with no success.
View Replies !
View Related
Insert Into Table A Select
I am testing out the innodb table types and I think that this statement should be able to work according to the transaction model for Innodb tables. create table a (c1 char(10) type=innodb; insert into a values ('aaa'); commit; insert into a select * from a; I get the following error: ERROR 1066: Not unique table/alias: 'a'
View Replies !
View Related
Select And Insert In One Process?
hi there, sorry if this sounds like a newb question - it is. please be gentle i have created a registration form using php which allows users to enter their info into a table called "retailer". one field entered is a postcode. there is another table, "domicile" which contains every postcode in the country as well as the mail centres that service each post code. there are say 10 postcodes per mail centre. during the registration process, i need a function that will use the postcode entered to look up the name of the relevant mail centre (from "domicile") and then store that name in the table "retailer" under the locale column. i can get the result i need with the query SELECT locale FROM domicile WHERE pcode=5555 LIMIT 1 where 5555 is the postcode. I am unsure though, as to how to properly implement this into my php code. PHP $physical_pcode = $_POST['physical_pcode']; $locale = @mysql_query('SELECT locale FROM domicile WHERE pcode=$physical_pcode LIMIT 1'); $sql = "INSERT INTO retailer SET physical_pcode='$physical_pcode', locale='$locale'"; but the field remains empty. Maybe I need to break this up into separate parts?
View Replies !
View Related
Combining An Insert And Select?
Is it possible to combine a select and insert statement into one statement? I would like to combine these two statements into a single database call. SELECT tx.account_balance as last_balance FROM transactions AS tx INNER JOIN ( SELECT MAX( txn_id ) AS last_tran FROM transactions WHERE user_id =63 ) AS m ON tx.txn_id = m.last_tran INSERT INTO transactions (user, value) VALUES ($user, last_balance+$amount )
View Replies !
View Related
|
TRACKER
