I'm working on creating a more efficient way to UPDATE random rows in a ticket allocation website we have. Currently, we pre populate the tickets table with ticket numbers in subsequent order, i.e 1,2,4 etc. Each ticket number being a row. When a customer then places their order we use the following SQL query to reserve their random tickets. UPDATE tickets SET order_item_id = X WHERE lottery_id = X AND order_item_id IS NULL ORDER BY RAND() LIMIT n To begin with the query above worked fine but as the table has grown we're now experiencing performance issues. The concept I have in mind is to change the population of the tickets table to populate the tickets table in a random order and then use the primary ID key in the tickets table instead to order by like so: UPDATE tickets SET order_item_id = X WHERE lottery_id = X AND order_item_id IS NULL ORDER BY id LIMIT n My question is, how much more efficient is this method compared to the RAND() function or is there any better ways of doing what I'm trying to achieve? **UPDATE** Because the ticket numbers are already pre populated in the tickets table at random when a competition is launched, i'm thinking the below could be a better solution? UPDATE tickets SET order_item_id = X WHERE lottery_id = '12102' AND order_item_id IS NULL AND id IN (SELECT id FROM tickets WHERE lottery_id = '12102' AND order_item_id IS NULL ) ORDER BY id LIMIT 3 Example of tickets table after tickets have been pre populated. | id | ticket_number | order_item_id | --- | --- |---| | 163895 | 1 | NULL | 163896 | 500 | NULL | 163897 | 1221 | NULL | 163898 | 20 | NULL | 163899 | 403 | NULL | 163900 | 8650 | NULL | 163901 | 2456 | NULL Ideally, it would be good it the first person who ordered 1 ticket got ticket number 1. And then if the next customer orders 2 tickets, they will get 500 and 1221 **ANOTHER UPDATE** After some experimenting i found this query to work quite well but would welcome some thoughts! UPDATE tickets SET order_item_id = '120' WHERE lottery_id = '12094' AND order_item_id IS NULL AND id >= (SELECT RAND()) LIMIT 3

According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp: https://www.postgresql.org/docs/9.4/static/uuid-ossp.html On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database. Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine? One scenario is the summer time / daylight savings.

A vendor application we're using utilizes multiple _processes_ (not threads) spread across multiple computers, one for each processing core available. When asked for a batch of calculations, the application distributes the load between these engines, which then go to the database for additional details necessary to perform the computations. As we add more cores -- and engines -- the simultaneous rush to the database causes a load spike so big, some engines start getting the ORA-12170: TNS:Connect timeout occurred error, which causes them to crash (I know, it shouldn't, but we cannot fix the vendor's code). For example, with the engine-count of 1386, about 300 engines dump cores, because the 16-core Oracle server cannot cope... There is no official way to cause the application to _stagger_ these engine-initializations... But the first thing each engine is doing is invoke the same stored procedure -- which updates the same table -- and I'm wondering, if I can alter it to perform the staggering: cause each engine to _sleep_ for random number of milliseconds. What would the SQL snippet for this look like? Our servers run Oracle-19...

I am building a MySQL database and think using implicit DEFAULTS seems very promising. I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for on insert timestamps and on update timestamps. I would like to add a default for a public key. A 32 character unique and random alphanumeric string. Is this possible using the DEFAULT field specification? If so, how? Or must I handle this by creating a random value and checking it's uniqueness, *for the 1 in 1.5^50 chance it's a duplicate*, on INSERT? This article seemed promising, but didn't give me an definitive answers: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/ Thank you in advance

How can I use SQL only on a MySQL DB to replace first and last names of real users in the users table using a random name from two related tables: random_first_names and random_last_names? Our users table contains over 250K records and each of the random tables contains over 5000 names that should be picked at random for each record in the users table. Is it possible to achieve this using SQL only?

I have one table with player ids and names. For each round the table will be scrambled, split in half, and put side by side. Handling of unequal amount of rows in each group is not the scope of this question. The only thing I need to achieve is (upper half + lower half) OR (table a + table b) side by side. In detail table a as is + both columns from b without any reference to each other - row by row. i prepared some simple fiddle: https://dbfiddle.uk/vkahsaFj https://dbfiddle.uk/ASM405Co Some references: https://stackoverflow.com/questions/7983369/how-do-i-combine-a-mysql-query-and-subquery-when-theres-multiple-rows The result must be this: I tried all type of joins, temporary tables, grouping and so on. without success.

In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'. How to do it? I have tried this way:

UPDATE users
SET id_shift = (
SELECT shift.id_shift
FROM shift
ORDER BY random()
LIMIT 1
);

I expected to have some users in one shift and others in another. But the result is that all users end up with the same shift.

I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column: postgres=# SELECT random() as sortOrder, random() as col2; sortorder | col2 -------------------+------------------- 0.607938482426107 | 0.121234225574881 (1 row) But if I add an ORDER BY clause, as shown below, then random returns the same random number each time for every row. postgres=# SELECT random() as sortOrder, random() as col2 FROM generate_series(0,1) ORDER BY sortOrder; sortorder | col2 -------------------+------------------- 0.100375576410443 | 0.100375576410443 0.170669795479625 | 0.170669795479625 (2 rows) I'm assuming there is a technical reason for this, but is it possible in this last query to get a different random value for each column? **Edit:** I may have oversimplified in my original question. The actual query I was attempting was more like the following: SELECT column1, random(), column2, random() FROM table1 ORDER BY random(); So, having an answer that can be combined with a table query is also important.

Currently I'm passing one value to my database, it's a random and unique 10-digit number. Is there a way I could generate this number directly in my database each time a new row is created? It looks something like this

id_Primarykey  Name  random number  
1              x     1231233592  
2              Y     5234234837  
3              Z     3453450394

Said number has to be unique, 10 digits, and random (non-sequential).

I am trying to generate random data (in volume) and was trying to use PostgreSQL's random() function with generate_series(), as shown:

SELECT
  (SELECT random()) AS a,
  (SELECT random()) AS b,
FROM generate_series(1,3);

However, **I get the same random values per row**:

a   |   b   
-------+-------
 0.124 | 0.443 
 0.124 | 0.443 
 0.124 | 0.443

How can this be solved?

As part of creating a machine learning exercise, I have an sqlite database that looks like this: The "Pizza", "DateDimension" and "Customer" tables are already populated, there are 5 different pizzas and 10 customers. How could I generate random data for the sales table, but give conditions to create a pattern? For example, on Monday/Tuesday a random sales figure between 15 and 25, on the other days of the week a random sales figure between 10 and 20, between 20 and 30 on the weekend. Knowing that for a day, a sale of 15 pizzas would be for the customer of ID=1 a sale of 2 pizzas of ID=2 and 4 pizzas of ID=3, for the customer of ID=2 a sale of 3 pizzas of ID=2, and
for the ID=3 customer a sale of 3 pizzas of ID=4 and 3 pizzas of ID=2. This makes a total of 15 pizzas, of which 8 ID=2 pizzas, 4 ID=3 pizzas, 3 ID=4 pizzas, divided between three customers. I don't know if I'm clear enough in my explanations, basically we need a random number of pizza sales for each day, and that this number is itself randomly split between the types of pizza and the customers, under certain conditions in order to follow a pattern in the number of sales per day.

I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below. Here is my code I use: WITH x AS ( SELECT mintwee, mineen, nul, pluseen, plustwee FROM Topic ) SELECT mintwee = CAST(RAND()*100 AS INT), mineen = CAST(RAND()*100 AS INT), nul = CAST(RAND()*100 AS INT), pluseen = CAST(RAND()*100 AS INT), plustwee = CAST(RAND()*100 AS INT) FROM x and this: WITH x AS ( SELECT mintwee = CAST(RAND()*100 AS INT), mineen = CAST(RAND()*100 AS INT), nul = CAST(RAND()*100 AS INT), pluseen = CAST(RAND()*100 AS INT), plustwee = CAST(RAND()*100 AS INT) FROM Topic ) SELECT mintwee, mineen, nul, pluseen, plustwee FROM x ; But both has the same result. I know, I must use an UPDATE statement, but this is only for try if the result is good. After it, I am going to use the UPDATE statement. I use SQL server with an express 2014 version.

The following will generate the same random number:

SELECT 
setseed(0.5), 
generate_series(1, 3) as id, 
round(random()::decimal, 2) as random_number;

Which outputs something along the lines of :

setseed | id | random_number 
---------+----+---------------
         |  1 |          0.25
         |  2 |          0.25
         |  3 |          0.25

I would like something such as

setseed | id | random_number 
---------+----+---------------
         |  1 |          0.11
         |  2 |          0.92
         |  3 |          0.38

And to be able to regenerate the above every time, as a result of setting a random seed. Basically: I don't want the numbers in the random_number field to be a single random number repeated across all rows, I'd like for each row to be a random number

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same everywhere. But the problem is, since this information is passed to third party vendors, they are able to trace, how many orders we are receiving per day. By sending a request in the early morning and sending another request in midnight and count the difference in the ID. This is giving insight information of our services. Since it's bit complex system, we can't do complex operation that can randomise this. My initial idea is to generate auto_increment numbers in bit random fashion way. 1,2,3,4,5,67,68,69,70,71,133,134,135,136,137,189,190,191,192,193,194 I guess it should be simple, as Mysql runs out of cache, generate next sequence by appending some random number. How to achieve this without impacting the performance?

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.

I always used to do: SELECT column FROM table ORDER BY random() LIMIT 1; For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started hunting for more efficient methods. People recommended: SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1; While fast, it also provides worthless randomness. It appears to always pick the same damn records, so this is also worthless. I've also tried: SELECT column FROM table TABLESAMPLE BERNOULLI(100) LIMIT 1; It gives *even worse* randomness. It picks the same few records every time. This is completely worthless. I need actual randomness. Why is it apparently so difficult to just pick a random record? Why does it have to grab EVERY record and then sort them (in the first case)? And why do the "TABLESAMPLE" versions just grab the same stupid records all the time? Why aren't they random whatsoever? Who would ever want to use this "BERNOULLI" stuff when it just picks the same few records over and over? I can't believe I'm still, after all these years, asking about grabbing a random record... it's one of the most basic possible queries. What is the actual command to use for grabbing a random record from a table in PG which isn't so slow that it takes several full seconds for a decent-sized table?

I need to assign random IDs to a table. Thus, I create a mapping table as CREATE TABLE t2 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, SourceID int(11) unsigned NOT NULL, UNIQUE INDEX(SourceID), PRIMARY KEY(ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1 and then added the IDs from the main table t1 as INSERT IGNORE INTO t2 (SourceID) SELECT ID FROM t1 ORDER BY RAND() For example, imagine t1 is the test results of the students, and we do not want to reveal the student ID (t1.ID) to reviewers (for an anonymous review). Then, we show each record with a new ID stored in t2. SELECT t2.ID AS NewID, t1.results FROM t1 JOIN t2 ON t1.ID=t2.SourceID The problem is t1 is tens of millions of rows and RAND() is very very slow. I do not need a perfect RAND() here; just assigning new IDs (somehow randomly arranged). Can you think of an approach to do query faster?

I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column? The function RANDOM( lower_bound , upper_bound ) apparently is restricted to only accept fix number rather than field / column name. --THIS ONE FAILS: WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS ( SELECT 'A', 0, 3 FROM CTE UNION ALL SELECT 'B', 1, 9 FROM CTE UNION ALL SELECT 'C', 2, 5 FROM CTE UNION ALL SELECT 'D', 1, 12 FROM CTE ), CTE(DUMMY) AS (SELECT 'X') SELECT T.*, RANDOM(T.VAL_MIN, T.VAL_MIN) --HERE'S THE PROBLEM FROM TRIAL T; --THIS ONE WORKS: WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS ( SELECT 'A', 0, 3 FROM CTE UNION ALL SELECT 'B', 1, 9 FROM CTE UNION ALL SELECT 'C', 2, 5 FROM CTE UNION ALL SELECT 'D', 1, 12 FROM CTE ), CTE(DUMMY) AS (SELECT 'X') SELECT T.*, RANDOM(1, 7) --HERE'S THE PROBLEM FROM TRIAL T; I'm pretty sure there's simple solution for this. Greatly appreciate any help.

Random write/read is slower than sequential for HDDs and some SSDs. Some database operations (UPDATE .. WHERE ..) may cause many random writes/reads. So I want to know if modern databases implement mechanisms to optimize them (e.g., innodb_flush_neighbors in MySQL ). The database can be relational, key-value, No-SQL, or any other kinds of databases.

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - 20 (random number between 1 and 100) - 30 (add random number 10 that's between 1 and 100) - 113 (add random number 83 that's between 1 and 100) - 118 (add random number 5 that's between 1 and 100) - 217 (add random number 99 that's between 1 and 100) - 220 (add random number 3 that's between 1 and 100) The data domains are often exposed via HTTP REST endpoints, and I would like the end users to not able to guess the primary keys by simply incrementing numbers. I'm trying to avoid UUID/GUIDs if possible. My REST URLs are longer, often with parent-child identifiers. (Yes, it's avoidable but I prefer it for simpler testing/troubleshooting). So I prefer numbers as identifiers. Is there any simpler solution that I'm not aware of? I'm using PostgreSQL but any generic solution is also fine.