Select 2 random null values in a Column and replace them with a value

Question

my table looks kinda like this

ID  Name Username
--  ---- ----
1   A    null
2   B    charmander
3   C    null
4   D    null

I want to select 2 random null values from column Username and replace both with pikachu.

Is there a way I can to this in C#?

Appreciate any help

*Is there a way* - the answer is typically "yes". Could you make the question slightly more involved? Show us what you've done, how you access your database, what you're stuck with.. — Caius Jard, Feb 16 '22 at 15:52
If your question amounts to "how to pick a random row from a SQL table?", see https://stackoverflow.com/a/3339298/224370 — Ian Mercer, Feb 16 '22 at 15:55

Laurenz Albe · Accepted Answer · 2022-02-16T19:19:28.933

2

UPDATE tab
SET username = 'pikachu'
WHERE id IN (SELECT id
             FROM tab
             WHERE username IS NULL
             ORDER BY random()
             LIMIT 2);

Yes, there are ways to run an SQL statement from C#, for example using the Npgsql provider.

edited Feb 16 '22 at 19:19

answered Feb 16 '22 at 15:56

Laurenz Albe

167,868
16
137
184

You can run a query via Npgsql, right? – Laurenz Albe Feb 16 '22 at 16:01
yeah ich used NpgsqlCommand command = db.CreateConnection().CreateCommand(). it worked with other spq statements. your sql statement looks very good but it says "Npgsql.PostgresException: '42601" Syntax Error :/ & the column username is a foreign key – RedDragon2001 Feb 16 '22 at 19:12
What exactly is the text message that goes with the 42601? – Laurenz Albe Feb 16 '22 at 19:14
Npgsql.PostgresException: '42601: Syntax error at »=« – RedDragon2001 Feb 16 '22 at 19:17
I had a typo in `LIMIT`, but that syntax error is not possible. Are you sure your query is exactly what I wrote? – Laurenz Albe Feb 16 '22 at 19:20
this is what i wrote :/ idk whats the prob – RedDragon2001 Feb 16 '22 at 19:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/242082/discussion-between-reddragon2001-and-laurenz-albe). – RedDragon2001 Feb 16 '22 at 19:23
1

You have to put spaces between the words. `"UPDATE cards" + "SET player"` is wrong; it should be `"UPDATE cards " + "SET player"`. – Laurenz Albe Feb 16 '22 at 19:24

score 0 · Answer 2 · answered Feb 16 '22 at 16:28

Assume that your table name is MyData. We create an instance of Random method to generate a random number of the list then we gonna replace the matching number of the index to pikachu

So try this out:

var random = new Random();

var usernameList = _context.MyData.select (x=>x.Username).ToList();

int index = random.Next(usernameList.Count);

var newList = usernameList .Select(s => s.Replace(usernameList[index],"pikachu")).ToList();


})

I've not tested this on my device but i think the concept will work for you!

NOTE: you may need to take care of converting int->string if needed. Also, try repeat the process for getting the second random element.

Select 2 random null values in a Column and replace them with a value

2 Answers2