Need help writing an insert query

[minli98]

Hi,

I have a database table DbTable containing 3 columns: Key1, Key2, and Value.

Now, in coldfusion, I have created a coldfusion query MyQuery that contains the same 3 columns. After I populate MyQuery with some rows, I would like to upload/insert into DbTable the rows whose 2 keys are not yet in DbTable.

It'll be something like this:

<cfquery name="insertToDBTable" datasource="myDSN">
    insert into DBTable
    select MyQuery.*
    from MyQuery
    where MyQuery.key1 and MyQuery.key2 are not in 
          (select key1, key2 from DbTable)
</cfquery>

Can anyone help me write a correct query for this problem?

Thank you!

Regards,
Min

 

[r937]

    insert into DBTable
    select MyQuery.*
    from MyQuery
    where NOT EXISTS
      ( select 1 
          from DbTable
         where key1 = MyQuery.key1 
           and key2 = MyQuery.key2 )

r937.com | rudy.ca

 

[minli98]

Hi Rudy,

Thank you for the help. I am not having luck puttin the code into <cfquery> though. When I do <cfquery datasource="mydsn">, it does not recognize MyQuery as an object. I have tried putting in # signs around MyQuery, but it still doesn't work.

Any thought?

Regards,
Min

 

[r937]

where does MyQuery draw its data from? may i see that query?

r937.com | rudy.ca

 

[minli98]

Hi Rudy,

Here's the basic run-down of what I am trying to do:

I want to know how many users look at both product X and product Y during the same session. So if user1 looks at products A, B, and C, while user2 looks at A and C, then the entries in the table will be (A,B,1);(A,C,2);(B,C,1).

So, in DBTable key1 and key2 are the two product keys and value is the number of users who look at both products during the same session.

The plan is to update DBTable once a day based on the session data. So after I extract the relevant data, I construct MyQuery and place my data there. Then I would merge the data in MyQuery with the existing data in DBTable.

The reason I use query is because I thought it would make the updating process a lot easier. I can use array or struct, but then I have to do multiple cfquery updates. If I can find a way to update DBTable from MyQuery, then I would call <cfquery> once only.

In this case the difficulty is on inserting product combination that are in MyQuery but not yet in DBTable.

I hope this makes sense to you. I'd be happy to clarify.

Regards,
Min

 

[r937]

So if user1 looks at products A, B, and C, while user2 looks at A and C, then the entries in the table will be (A,B,1);(A,C,2);(B,C,1).

i am sorry, but i don't get it


the visitor is looking at two products simultaneously? why are there two keys in the table along with the user number?


r937.com | rudy.ca

 

[minli98]

The basic idea is we want to find out if a user likes product A, what other products might he like. We try to answer this by looking at user session data. If a lot of users look at both products A and C during their sessions (not simultaneously), then we know that product C goes well with product A.

So the two keys represent the combination pair of products.

I hope this clarifies your question.

Regards,
Min

 

[r937]

i understand the objective

i just don't understand how

user 1 looks at products A+B+C

translates into

(A,B,1),(B,C,1)

you store user1's behaviour two products at a time?

so if your data is (A,B,1),(B,C,1),(C,D,1)

then you want to know that A related to D?

i'm still confused





r937.com | rudy.ca

 

[minli98]

Let's say User 1 looks at A and C. The table will then have:
(A,C,1), (C,A,1)

User 2 comes along and looks at A, B and C. The table is now:
(A,B,1), (A,C,2), (B,C,1),
(B,A,1), (C,A,2), (C,B,1)

User 3 comes and looks at A and D. The table will then be:
(A,B,1), (A,C,2), (B,C,1), (A,D,1)
(B,A,1), (C,A,2), (C,B,1), (D,A,1)

Say I want to know what product goes best with A. So I look at all the rows with A in the first column. They are:
(A,B,1),(A,C,2),(A,D,1).

Since (A,C) has the highest value, I would conclude that C is the answer.

Similarly, if I want to know the product that goes well with B, I look at the rows where B is in the first column: (B,A,1), (B,C,1).

Since (B,A) and (B,C) have the same value, A and C are the answer.

Does this make sense?

Rgds,
Min

 

[r937]

okay, yes, now it finally makes sense

(you threw me a curve ball with user[!]1[/!] and user[!]2[/!] and (A,B,[!]1[/!]);(A,C,[!]2[/!]);(B,C,[!]1[/!]) ;-) )

okay, now let's go back to your problem, and instead of saying you want to populate a query, or insert into a table, could you please just explain what information you want from this table


r937.com | rudy.ca

 

[minli98]

Rudy,

I am happy to tell you that after several trial-and-errors, I got my code to work. Sorry I didn't explain my problem well.

I was trying to update a database table with data from a coldfusion-generated query, but not before I do some comparison checks between the table and the query. I was trying to do it in one step and I couldn't get it to work. I got it working after I made it into a 3 step process: 1)Download the data from database to CF, 2)Do the comparison checks against the CF-generated query and put the results in a new query, 3)Upload the data from new query to the database.

Thank you for all your effort! Have a nice weekend.

Regards,
Min