LOAD wont work so... 1

[nat1967]

Hi everyone,

i am stuck and need some advice or direction.

i have a web store that has about 1000 items in different catagories. i want to add a new catagory with 300 records. This new catagory is EXACTLY like one of my existing catagories with just one exception.

Currently, I must input each record in the database for this new catagory manually. that is a slow process!!!!

What i would like to be able to do is build a query to select all the records in the existing catagory that matches 95% of what i need. make a change to one column, and insert this "new" recordset back into the products table. this would certainly be a whole bunch easier than manually inputing each new record.

i can build select queries with no problem. but... i have no idea how to accomplish select, change, insert at the same time.

any ideas? hope i have not made this confusing.

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[BNPMike]

This seems like a very elementary SQL problem. I doubt anyone can give you the code here, as the exact nature of what you want to do is not clear. You'd be quicker getting a basic SQL text. You'll need to know SQL in due course anyway.

By the way, why did you refer to "load" in your title but not in the text?

 

[nat1967]

HI BNPMike,

I refered to LOAD because I have been trying to use the LOAD DATA INFILE or LOAD DATA LOCAL INFILE through MySQL query interface my ISP provides. I am now being told from my ISP that this option is not activated for security reasons.

So, I am now trying to find alternatives.

In therory, this is 3 SQL statements. 1 - Select 2-Update 3-Insert. My problem is how to combine all three into 1 step. I can and do use SQL daily but in MS Access. In Access, I would create a temp table, make my changes, then insert the info back to the main table. No so bad since I can have my "hands" on the database. MySQL still scares me a bit and there must be an easier way.

I need to be able to select an existing recordset. Make changes to the records and then insert the "new" recordset back into the table as NEW records. I do not want to permanently modify the existing records which is what would happen if I used an Update query alone.

I am really just looking for some ideas to get "out of my Access box". Any suggestions are greatly appreciated.

Have A Great Day!!!,

Nathan
Project Manager III

http://www.skylineprints.com

 

[TonyGroves]

How about something like:
[tt]
INSERT tbl (keyfield,category,field2,field3)
SELECT 1000+keyfield,7,field2,field3
FROM tbl WHERE category=6
[/tt]
This assumes that:
- The products are primary-keyed on a numeric field keyfield and adding 1000 to each value would produce new product codes. You would obviously a different system to suit your situation.
- The existing category number is 6, the new category number is 7.

Then you would edit the new records to suit.

-----
ALTER world DROP injustice, ADD peace;

 

[nat1967]

Tony, what can i say....

WHOA!!!!!!!!!!!!!!!!

my god ... i have been going around the world. no wonder i am so tired at work.

Thank you very much. your example has shown me the light. it makes sense now. worked like a charm.


Have A Great Day!!!,

Nathan
Project Manager III

http://www.skylineprints.com