SQl query help

[kronnos]

Can someone help with a query to import data into a subscriptions table. It shoudl only update but not overwrite if data for that user already exists.

1) I would either want to migrate date from a certain table>field to a different table>field where the user ID filed matches.

Or the longer way...

2) Export all the data from the 3 different tables that make up all the subscription data and then somehow import that back into the default vBulletin subscription tables.

Any advice or help with query?

Thanks in advance!

[kh99]

I don't quite understand what you need. Are you talking about adding records to your subscribeforum and subscribethread tables? Where would the info be coming from?

[kronnos]

Ok after about 6 hours, I think I figured out exactly what fields i would need to move the data from and to.

I would like to move data from the "From_subscriptions_data" table to the "To_subscriptionlog" table. I specified the default values for field that would not be imported from the "From_subscriptions_data" table.

The next move is from the one table "From_payments" to two different table called "To_paymenttransaction" and "To_paymentinfo". A lot of the field should just have default data that i specified.

Below is the chart with the table names and the fields.
Looks a lot cleaner here: https://docs.google.com/spreadsheet/ccc?key=0Ag478yKi_ZWudHJrVkllY19qV1NBeW01bWI3ODFhN 2c

To_subscriptionlog ----- < ----- From_subscriptions_data
subscriptionlogid --------- < ----- id
subscriptionid ------------ < ----- subid
userid ------------------- < ----- userid
pusergroupid ------------ < ----- termid
status ------------------ < ----- active
regdate ----------------- < ----- stime
expirydate -------------- < ----- etime
importsubscriptionlogid --- < ----- (Set all to "0")


To_paymenttransaction --- < --- From_payments
paymenttransactionid ------- < --- (Generate next available number in this column)
paymentinfoid -------------- < --- (Generate next avaliable number in this column)
transactionid --------------- < --- txn_id
state ---------------------- < --- (Set all to "1")
amount -------------------- < --- mc_gross
currency ------------------- < --- mc_currency
dateline -------------------- < --- payment_date
paymentapiid --------------- < --- (Set all to "1")
request -------------------- < --- (Set all to "NULL")
reversed ------------------- < --- (Set all to "0")
To_paymentinfo -----------<
paymentinfoid -------------- < --- (Same generated number that goes to the paymentinfoif field in To_paymenttransaction table )
hash ----------------------- < --- (Set all to "Imported")
subscriptionid --------------- < --- (Set all to "1")
subscriptionsubid ------------ < --- ("2" IF above field mc_gross is 4, "1" IF above field mc_gross is 6, "0" IF above field mc_gross is 10)
userid ---------------------- < --- userid
completed ------------------ < --- (Set all to "0")


The link below also has some sample data using a specific record where data was once moved the opposite way but it might be a bit confusing and the above hopefully has enough information.

https://docs.google.com/spreadsheet/ccc?key=0Ag478yKi_ZWudHJrVkllY19qV1NBeW01bWI3ODFhN 2c&rm=full#gid=1

Again, thanks for all the help!

[kh99]

OK, a couple of things: I think the first move can be done pretty easily with an SQL query, but in the first post you say "It should only update but not overwrite if data for that user already exists". To do that I think you'd need to have a key defined that includes userid, or else there won't be anything stopping multiple rows with the same userid (I think - I'm not actually an SQL expert or anything).

For the second one, the generated numbers are a bit of an issue. Normally you'd make the id fields AUTO_INCREMENT in one of the tables, then if you needed the value for another table you'd get the last inserted value (which I could do in php but I don't know how to do in SQL).

I might be able to take a shot at a php script that handles this. Someone else who's better at SQL than I am might be able to do it all with one or more queries. Also, since this doesn't look like it's vbulletin-specific, you could ask at some place like stackoverflow.com.

[kronnos]

I apologize, I think I actually do want it to overwrite values (especially the "expirydate -------------- < ----- etime"). However, like you suggested, the key for the first move should be userid because that value has to be unique. So for example if a row already exists with that userid, it should just update the rest of the field and not create a totally new entry with a duplicate userid in the "To_subscriptionlog" table.

I agree that the second move looks a lot more complicated. The only common field in "To_paymentinfo" and "To_paymenttransaction" is paymentinfoid. Would it be possible or easier to somehow create the 2 tables from the 1 independently of each other?:

e.g.

To_paymentinfo -----------< --- From_payments
paymentinfoid -------------- < --- (Generate next avaliable number in this column)
hash ----------------------- < --- (Set all to "Imported")
subscriptionid --------------- < --- (Set all to "1")
subscriptionsubid ------------ < --- ("2" IF above field mc_gross is 4, "1" IF above field mc_gross is 6, "0" IF above field mc_gross is 10)
userid ---------------------- < --- userid
completed ------------------ < --- (Set all to "0")


And once the above is created:

To_paymenttransaction --- < --- To_paymentinfo
paymenttransactionid ------- < --- (Generate next available number in this column)
paymentinfoid -------------- < --- paymentinfoid
transactionid --------------- < --- txn_id field from the "From_payments" table record where userid=userid from "To_paymentinfo"
state ---------------------- < --- (Set all to "1")
amount -------------------- < --- mc_gross field from the "From_payments" table record where userid=userid from "To_paymentinfo"
currency ------------------- < --- mc_currency field from the "From_payments" table record where userid=userid from "To_paymentinfo"
dateline -------------------- < --- payment_date field from the "From_payments" table record where userid=userid from "To_paymentinfo"
paymentapiid --------------- < --- (Set all to "1")
request -------------------- < --- (Set all to "NULL")
reversed ------------------- < --- (Set all to "0")

Its just a suggestion and I dont even know if its possible to perform such statements as above (like VLOOKUP) and reference other tables but I really don?t know much SQL besides the basic queries.

Thank you for looking in to this and all help is much appreciated.

[kh99]

Well, for the first one I think you could do this:

REPLACE INTO To_subscriptionlog (subscriptionlogid, subscriptionid, userid, pusergroupid, status, regdate, expirydate, importsubscriptionlogid)
SELECT id, subid, userid, termid, active, stime, etime, 0 FROM From_subscriptions_data


but I haven't tested it. That will replace lines with duplicate keys. If you wanted to ignore lines with duplicate keys you could change REPLACE to INSERT IGNORE. Edit: but without any "order by" on the select you don't know which order you'll be inserting the records, so if you have rows in From_subscriptions_data that would result in duplicate keys in To_subscriptionlog, you probably need to figure out a better way to handle it.


If afraid the rest of it is beyond my SQL ability. Hopefully someone else will help.

[kronnos]

That's, this looks good!

I just had some other feedback on a possible workaround to the second import.

Right before doing the migration, create a query that would add a "paymentinfoid" to the original table. This way the second import can simply be treated as 2 separate imports.

Unfortunately, I was not give any examples of how the actual queries would look.

How would I create an additional colum that would AUTO_INCREMENT from a number I specify (After i check for last number in "To_paymentinfo" table)?

Also, do do this:
subscriptionsubid ------------ < --- ("2" IF above field mc_gross is 4, "1" IF above field mc_gross is 6, "0" IF above field mc_gross is 10)

I would probably end up running 3 different queries based on the number in the mc_gross field. Would I be able to create a query that says "Only run IF that field value is x?

[kh99]

How would I create an additional colum that would AUTO_INCREMENT from a number I specify (After i check for last number in "To_paymentinfo" table)?

You can do this to change the next value of an autoincrement column:
ALTER TABLE tablename AUTO_INCREMENT=X


Also, do do this:
subscriptionsubid ------------ < --- ("2" IF above field mc_gross is 4, "1" IF above field mc_gross is 6, "0" IF above field mc_gross is 10)

I would probably end up running 3 different queries based on the number in the mc_gross field. Would I be able to create a query that says "Only run IF that field value is x?

You could use CASE:
CASE mc_gross WHEN 4 THEN 2 WHEN 6 THEN 1 ELSE 0 END


I think that can be used in place of mc_gross when listing columns in a SELECT.

[kronnos]

Thanks!