Select and insert at the same time

insert into select
sql insert and select at the same time
sql insert with join
insert into select mysql php
convert insert statement to select
mysql insert into select
mysql insert multiple rows from select
db2 insert into select

So, i need to get max number of field called chat_id and after that i need to increment it by one and insert some data in that field, so the query should look something like this:

SELECT MAX(`chat_id`) FROM `messages`;

Lets say it returns me 10 now i need to insert new data

INSERT INTO `messages` SET `chat id` = 11 -- other data here....

So it would work the way i want but my question is what if betwen that time while i'm incrementing and inserting new record other user gonna do the same? than there would already be record with 11 id and it could mess my data is there a way to make sure that the right id goes where i need, btw i can't user auto increment for this.

EDIT as i said i cannot use auto increment because that table already have id field with auto increment, this id is for different porpuse, also it's not unique and it can't be unique

EDIT 2 Solved it by redoing my whole tables structure since no one gave me better ideas

If you cannot use an auto-increment primary key then you will either have to exclusively lock the table (which is generally not a good idea), or be prepared to encounter failures.

Assuming that the chat_id column is UNIQUE (which it should be from what you 're saying), you can put these two queries inside a loop. If the INSERT succeeds then everything is fine, you can break out of the loop and continue. Otherwise it means that someone else managed to snatch this particular id out of your hands, so repeat the process until successful.

At this point I have to mention that you should not actually use a totally naive approach in production code (e.g. you might want to put an upper limit in how many iterations are possible before you give up) and that this solution will not work well if there is a lot of contention for the database (it will work just fine to ensure that the occasional race does not cause you problems). You should examine your access patterns and load before deciding on this.

How to insert and select query at the same time in mysql?, i suppose you can use below statement : INSERT INTO blog (title,desc) VALUES ('blog1','description') SELECT * FROM blog ;. # Select the new record. select * from messages where chat_id = new_id; The MySQL's documentation on uuid() says: A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate devices not connected to each other.

Don't try to do this on your own. You've already identified one of the pitfalls of that approach. I'm not sure why you're saying you can't use auto increment here. That's really the way to go.

CREATE TABLE messages (
    chat_id INT NOT NULL AUTO_INCREMENT,
    ....
)

Insert and select on a table at the same time – SQLServerCentral, Hi,. I have a job A which inserts data into a table and another job B which selects data from the same table .Both the jobs run at the same time. I am trying to select and update a row at the same time and trying to accomplish these two queries simultaneously. LAST_INSERT_ID. Barry Galbraith.

AUTO_INCREMENT would solve this problem. But for other similar situations this would be a great use of transactions. If you're using InnoDb engine you can use transactions to ensure that operations happen in a specific order so that your data stays consistent.

SQL, The INSERT INTO SELECT statement is used to add multiple new records into a database table at one time. The number of rows that you can insert at a time is 1,000 rows using this form of the INSERT statement. If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later.

You can solve this by using MySQL's built-in uuid() function to calculate the new primary key value, instead of leaving it to the auto increment feature.

Alter your table to make messages.chat_id a char(36) and remove the AUTO_INCREMENT clause.

Then do this:

# Generate a unique primary key value before inserting.
declare new_id char(36);
select uuid() into new_id;

# Insert the new record.
insert into messages
(chat_id, ...)
values
(new_id, ...);

# Select the new record.
select *
from messages
where chat_id = new_id;

The MySQL's documentation on uuid() says:

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate devices not connected to each other.

Meaning it's perfectly safe to use the value generated by uuid as a primary key value.

This way you can predict what the primary key value of the new record will be before you insert it and then query by it knowing for sure that no other process has "stolen" that id from you in between the insert and the select. Which in turn removes the need for a transaction.

Insert the same time, it is possible to happen?, Theoretically you could end up with duplicate usernames. In a scenario of high incoming requests, a race condition could occur. If you want to  Click in cell A1 in Sheet1, and then type: This data will appear in each sheet. TIP: To paste into multiple sheets at the same time, first paste the data into one of the sheets, and then select the range that contains the data you want to paste to the other sheets and use Fill > Across Worksheets.

SQL Server INSERT Multiple Rows Into a Table Using One Statement, The number of rows that you can insert at a time is 1,000 rows using this form of the To insert multiple rows returned from a SELECT statement, you use the  Note that this is the simplest form. The SELECT statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.. Please note the number of columns specified in the SELECT statement must be the same as the number of columns specified in the INSERT INTO statement.

4. Inserting, Updating, Deleting - SQL Cookbook [Book], To that end, you'll also find techniques for inserting many rows at a time. Likewise​, updating and 1 create table dept_2 2 as 3 select * 4 from dept 5 where 1 = 0  Example 4: Insert using both columns and defined values in the SQL INSERT INTO SELECT Statement. In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to get records from the source table and insert it into the destination table.

13.2.5.1 INSERT SELECT Syntax, When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows  INSERT and SELECT INSERT and SELECT statements are used to add rows to a table. The SELECT statement retrieves or extracts information from tables in the database. Using the SELECT statement, you can choose the rows in a table that you want returned from a query by specifying values to match the rows that you want to display.

Comments
  • please see the similar question. stackoverflow.com/questions/1587562/…
  • After seeing your edits, it sounds like you do not have a separate chat table. If not, you should make one.
  • @jon even if i do create seperate table problem would be the same i still can't use auto increment or unique
  • @Linas: You can use auto_increment on the new table though, which will solve your problem. After the new id has been inserted in chat you know that it is unique, so you can use it as messages.chat_id as well without fear of race conditions.
  • I don't want to downplay the good sense in this answer, but the OP explicitly mentions that they cannot use auto-increment.
  • @Jon: I saw that and would love to hear OP's reasoning for why they cannot use it.
  • Me too. But IMHO it would be more appropriate to ask so in a comment before suggesting an explicitly disallowed solution.
  • Transactions are not a magic wand that will automatically solve your problems. You have to consider which transaction isolation level (if any) is suitable.