0

I'd like to insert a record using values from different tables

just to illustrate:

INSERT INTO tbl1 
VALUES([value1], [value2], NOW())
  • value1 is a number that comes from a cell in a random row in a different table
  • value2 is another number that comes from a cell in a random row in another table

Here's how I'm trying to do it:

INSERT INTO transactions(itemid, userid, tdate) 
VALUES((SELECT TOP 1 ID FROM items ORDER BY RND(ID)), (SELECT TOP 1 ID FROM users ORDER BY RND(ID)), (NOW()))

But this throws an error:

Query input must contain at least one table or query

Any help would be greatly appreciated.

Thanks!

Rian
  • 171
  • 16
  • How does this not work? If you want to insert a text field why are you using a date/time function. – Gordon Linoff Mar 12 '16 at 15:08
  • @GordonLinoff I mean to use the current date as the third parameter. Sorry the examples were not consistent. I'll update the question now. – Rian Mar 12 '16 at 15:15
  • Add the structure of your table. That will tell us if your `tdate` column is declared as `date` or `text`. Additionaly what does that mean "_it doesn't do it_" ? Do you have an error message or incorrect data inserted ? – Thomas G Mar 12 '16 at 15:15
  • @ThomasG question updated – Rian Mar 12 '16 at 15:23
  • In which context are you putting this SQL? A query object? – trincot Mar 12 '16 at 15:24
  • @trincot right now, I'm just testing it in MS Access but I plan to execute this query from a windows form application in vb.net – Rian Mar 12 '16 at 15:28
  • 1
    What do you want `SELECT TOP 1 ID FROM items` to give you? Would that be equivalent to `Min(ID)`? – HansUp Mar 12 '16 at 15:33
  • @HansUp This will give me the value of the column 'ID' from the first record(Top 1) in the table. No problem with this. This works fine – Rian Mar 12 '16 at 15:37
  • 2
    @Rian Find a database expert you trust. Ask him or her whether `SELECT TOP 1` is reliable without an `ORDER BY` – HansUp Mar 12 '16 at 15:40
  • 2
    @Rian You probably need an `order by` to guarantee anything about which *ID* you get back. This all looks like a bad idea all around. – shawnt00 Mar 12 '16 at 15:41
  • @HansUp Sorry. I thought the ORDER BY part would just complicate the question so I did not included it. Please check the update Thanks! – Rian Mar 12 '16 at 15:47
  • So now you're ordering them randomly? Just use trincot's answer if that's what you wanted. Are you just populating some test data or something? – shawnt00 Mar 12 '16 at 15:49
  • @shawnt00 Yes but I just need the randomization to get a random user ID and a random item ID, and then use them to create a record in my 'transactions' table. Yes, I'm just populating a test data. – Rian Mar 12 '16 at 15:53

1 Answers1

3

You could rewrite your statement to use a SELECT instead of VALUES:

INSERT INTO  transactions(itemid, userid, tdate) 
    SELECT   TOP 1 items.ID, users.ID, NOW() 
    FROM     items, users
    ORDER BY Rnd(-(1000*items.ID*users.ID)*Time()), 
             items.ID, users.ID

Edit: I added the ORDER BY clause which will lead to more random sort orders. The negative value will ensure a sort of randomize. See also this question.

Edit2: extended ORDER BY clause to ensure TOP 1 will not have to deal with ties.

Community
  • 1
  • 1
trincot
  • 317,000
  • 35
  • 244
  • 286
  • can't use a join because there is no direct link between the row I'm getting the item ID from and the row I'm getting the user ID from. Condition is also not necessary. Thanks for the input though – Rian Mar 12 '16 at 15:34
  • Testing the updated solution now. Will get back to you shortly. Thanks! – Rian Mar 12 '16 at 16:05
  • This adds records using random user ID and item ID from my other tables, which what I am trying to accomplish. The only problem is that when I tried to execute it multiple times, sometimes it added 1 row, sometimes it added 2, and sometimes 3. Any idea why? – Rian Mar 12 '16 at 16:11
  • @HansUp I don't follow why there are duplicates and ties (assuming that the other tables have a proper primary key.) – shawnt00 Mar 12 '16 at 16:21
  • @shawnt00 I think I went astray, so removed those comments. Sorry. – HansUp Mar 12 '16 at 16:28
  • If the lowest random value in the `ORDER BY` clause happens to be the same for two records, you'd get two. I added in my answer both ID values to the ORDER BY clause. This should (hopefully) exclude ties. – trincot Mar 12 '16 at 16:29
  • @trincot So `TOP 1` can return ties? Weird. – shawnt00 Mar 12 '16 at 16:36
  • @trincot the last update works prefectly! Now I just need to spend some time studying the solution and find out how exactly it works :) You are awesome! Thank you very much – Rian Mar 12 '16 at 16:40
  • @trincot Just have one quick question. Why do we get two records when the lowest random value in the ORDER BY clause are the same even when we are using TOP 1? – Rian Mar 12 '16 at 17:36
  • Ooops! Never mind. I think I've got the answer from here: [link](https://msdn.microsoft.com/en-us/library/bb177882%28v=office.12%29.aspx ) Thanks anyway – Rian Mar 12 '16 at 17:40