I need a SQL query that adds price and size data to a database

Posted on 2011-03-03
Medium Priority
Last Modified: 2012-06-22
I need a query to add price and size data to a table. The name of the table is AttributeDetail.
Question by:philip38
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
LVL 22

Expert Comment

ID: 35031915
I don't seem to have anything that will open the file types that you have attached.  Can you, perhaps, attach Word (or some other standard file type) equivalents?

Does your AttributeDetail table have columns for the price and size data?

I'm not at all sure how this is a difficult task . . . but, then again, I can't see the information you have provided. ;-)

Author Comment

ID: 35032212
Oh, Thank You.  Here attached are word files. At the office I only had the Open Office Draw/Suite programs.
LVL 22

Expert Comment

ID: 35032363
Okay, I can see the files now (which really helps ;-).

However, I don't exactly see how you plan to add the price and size information to that table . . . there don't appear to be any columns provided for some of the information.

I am assuming that the first set of valuies ("Size") is associated with an unframed image while the second is associated with framed images.  If that is true, then you are dealing with something like the following:
Image_Size	UnFramed	Framed
8.5x11	$60.00	NULL
11x14	$22.00	$ 95.00
16x20	$40.00	$140.00
18x24	$50.00	$180.00
20x28	$60.00	$225.00
26x36	$60.00	$295.00

Open in new window

Now, I am not at all sure how the colors come into play with the pricing.  And I am not at all sure how you are using the Attribute table.  Is the Attribute table used to indicate what attributes something is supposed to have?  And, then, are you somehow wanting to transfer the size/pricing information into the the AttributeDetail table?

Personally, I would tend to have a ImagePrices table with the Data, shown above, arranged in that manner (with a ImagePriceID column that is an Identity column as the primary key).  Then I would use the ImagePriceID to link from any Inventory/OrderDetail/whatever entries.

The use of the Attributes and AttributeDetail tables looks like a bit of over kill in the area of normalization to me.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 35032930
Makes sense to me. I will have to run it by the guy who assigned this to me tomorrow. (Flooded with work). Really appreciate it. Please stand by.
LVL 50

Expert Comment

ID: 35034345
its difficult to understand what you are trying to do since you haven't given any background on the table relationships
or even the table structures...

i'd say that possibly your tables are wrongly named since it appears that attributes is really a productAttribute table
(similarly with attributedetail?).. are you sharing attributes between products ?  is there another table which details
the cross attribute relationships (e.g. you can't have a black frame above size X? or Pink frames are only in size Y)

are you updating existing rows with price info
or inserting new rows ?

do you have attributes already set up for FRAME ?

Author Comment

ID: 35038620
Thank you for your reply.
Let me enhance the explanation with another file. I have created a diagram of the key tables showing their relationships.

Also, I want to show you the end result page we are talking about. You can find it on the web here:


Author Comment

ID: 35038636
That is to say http://www.ballparkprints.com/detail.aspx?ID=24 is a sample of the page that needs to have the prices and sizes updated.
LVL 22

Assisted Solution

8080_Diver earned 400 total points
ID: 35039222

Okay, in looking at your diagram, I am still trying to figure out where your size and cost figures go.  Are the separate entries in the AttributeDetail table or what?

I still think that the separate ImagePrices table would be the best bet.
Table Name: ImagePrices

Column Name            Datatype
ImagePriceID             INT (Identity)
ProductID                   INT   (FK to Products table)
ImageSizeID              INT   (FK to ImageSizes table)
ImageCost                 Currency

Table Name: ImageSizes
Column Name            Datatype
ImageSizeID               INT (Identity)
ImageSizeDesc         VarChar(50) (Description of ImageSize)

Open in new window


Author Comment

ID: 35039677
I think that is a viable suggestion, except I want to if possible keep working on the database as it is constructed.
Therefore, I have attached an enhanced bitmap of the database diagram. These include the tables that have the 'price' field.

My objective is to 1. change existing prices on the page and 2. add new size item entries to the page.
LVL 50

Accepted Solution

Lowfatspread earned 1600 total points
ID: 35040282

i still think mostt of your problem is down to mis nameing of the tables, and i don't like how you've got "price" scattered about ...

but it is fairly simple to update your price

insert into attributedetails (list of table columns)
 values ( the values for each column)


insert into attributedetails (productid,attributeid,name,price,....)
 values (12,13,'11x8',49,00,...)

assuming that you have converted 'size' to its attributeid  e.g. 13
(framed is also an attribute in your system and it has names which are a subset of the set of names available to size(?))

but it starts to breakdown with colours... which (i think) is where our confusion comes in ...  you are missing a table which
controls the ability to mix the options...
 size, framed, color of frame, etc   ...

your order table has options for gift wrapping etc , i'm not sure why that is being treated differently from (say) framing...
you also have a saleitem indicator , but again is that at the correct place (could you not just have a sale on "pink" frames?)

 perhaps a better model for you to think of would be a pizza creation/order menu....

each component can be separately charged/changed/customised and the price is determined by having a Basic price (size)
to which the other product customisations then add to (e.g. sweetcorn,pepparoni,anchovies 0.50 each, Tuna,beefm,, 0.75)
then you get the options on crust, base type, how cut, delivery etc....


Author Closing Comment

ID: 35057564
Thank you both to Lowfatsrpead and to 8080 Diver
LVL 22

Expert Comment

ID: 35058652
I have to agree with Lowfatspread that having pricing information in 4 tables is a bit problematic.  I can see having it in the Product Table and in the OrderDetails table (because the price could change after the OrderDetails entry is created) but having it in the other tables looks a whole lot like the design needs more work.

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question