Ad:

MS SQL Discussion Thread
Marked Solved
Views: 667

Mar 7th, 2009

SQL Query Help

Expand Post »

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

Similar Threads

sql query problem with MS Access and C# in C#
Urgent Help with SQL Query in MySQL
C# VS 2005 - SQL Query Parameters to an ODBC DataSource in C#
sql query updating problem in Visual Basic 4 / 5 / 6
Javascript array from sql query in JSP

pezza

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Mar 2007

Permalink

Mar 8th, 2009

Re: SQL Query Help

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Mar 8th, 2009

Re: SQL Query Help

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Mar 2007

Permalink

Mar 8th, 2009

Re: SQL Query Help

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

pezza

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Mar 2007

Permalink

Mar 10th, 2009

Re: SQL Query Help

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...

MS SQL Syntax (Toggle Plain Text)
 
declare @NewOptionGroup int  
      , @CopyFromOptionGroup int  
SELECT @NewOptionGroup=9   
     , @CopyFromOptionGroup=7   
 
INSERT INTO XRef (idProduct,idOptionGroup)   
SELECT idProduct,@NewOptionGroup   
FROM XRef   
WHERE idOptionGroup=@CopyFromOptionGroup
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

pezza

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Mar 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: I can’t install SQL Server 2008 Express with Advanced Services - Help!

Next Thread in MS SQL Forum Timeline: Question about MS SQL server certificates

DaniWeb Message

Cancel Changes

User Name
Password