SQL Query Help

[pezza]

Hi,

I hope I am in the right place for this...

I am looking for some help with a query. Basically, I am using a shopping cart which has option groups and I have created 2 new option groups. I now need to add these option group id's to all products where the product already includes another option group id. For example, a lot, but not all of the products have an option group called "Embroidery Text" (which has an id of 7). I have just created another group called "Embroidery Location" (12) and want to add this group to all of my products that contain the group "Embroidery Text".

The database stores the option groups in a table called "OptionsGroupXRef" and this table contains a unique id, the id of the product and the id of the option group.

So, in english, i need to say something like insert into OptionsGroupXRef, idOptionGroup of 12 and idproduct WHERE the idProduct contains idOptionGroup 7

I know that this is completely wrong in SQL speak, but this is where i need help. Yes i can filter the table for the idOptionGroup 7 and then manually insert a row for each product, but there are over 1000 products, and therefore it would take me ages to do that.

Can anyone help me with this, or point me to a forum that may be able to help?

Thanks

Andrew

[Geek-Master]

Sounds more like you need to update the existing records that have a group id of 7 and change those to 12, but if you need both groups to be related to the same products as ID 7, then an insert command will suffice. If you do a google on "T-SQL Insert and Update" commands you will find what you need.

[pezza]

It would definately need to be an insert, otherwise if i update the record, that will just replace the option group with the other and i need both to be linked to the product. It is just that 7 is Print Text and 12 is Print Location, therefore i need to find which product id's have got option group 7 linked to them and then add option group 12 to those product. The table will end up with:

id idproduct idoptiongroup
1 16 7
2 16 12

The database is not related very well, and makes it very difficult to extract information from across other tables, but i do not have the skills...yet... to develop my own shopping cart and database, and the cart did only cost me $70 so, i can't complain too much :-)

I will have a search on google for the commands though, it is just that part of telling the query to insert product ids only where there is a record with that particular option group which is baffling me.

Thanks for the info.

Andrew

[pezza]

I ended up solving this semi manually:)

I just filtered out the records with the option group id's, exported in to excel, added the new option group id against the product id's and then imported it back in.

Probably cheating a little but it got the job done quicker than finding the correct SQL statement.

Managed to work out another task that i had though to update the prices by a percentage for every product, so i at least achieved a little bit of SQL knowedge :icon_biggrin:

[pezza]

Hi,

I just thought i would share the correct solution to this. Thanks to Brad Schulz from the MSDN community forums for providing me with the following...

declare @NewOptionGroup int  
      , @CopyFromOptionGroup int  
select @NewOptionGroup=9   
     , @CopyFromOptionGroup=7   
  
insert into XRef (idProduct,idOptionGroup)   
select idProduct,@NewOptionGroup   
from XRef   
where idOptionGroup=@CopyFromOptionGroup

I am new to all this, so i don't think i needed to declare the @CopyFromOptionGroup as 7, maybe just specified this within the query itself as 7, but seems though i am new, and it worked, i am not in a position to query it :-)

Anyway, just thought i would share the answer in case any others need to know this.

Thanks

Andrew