Solved

MySQL - get data from one table to another

Posted on 2014-12-27
5
262 Views
Last Modified: 2014-12-28
I have an table called 'items' with fields 'id' and 'itemPrice'
What SQL statment should I use to alter my cart table below with itemPrice from my items table if no price exists in cart table?

So basically I need to get itemsPrice from items table where cart.items_id = items.id

Thanks in advance

CREATE TABLE IF NOT EXISTS `cart` (
  `cartId` int(11) NOT NULL AUTO_INCREMENT,
  `cookieId` varchar(50) DEFAULT NULL,
  `items_id` int(11) DEFAULT NULL,
  `spec1` varchar(40) DEFAULT NULL,
  `spec2` varchar(40) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `comments` tinytext,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`cartId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=763 ;

--
-- Dumping data for table `cart`
--

INSERT INTO `cart` (`cartId`, `cookieId`, `items_id`, `spec1`, `spec2`, `qty`, `price`, `comments`, `timestamp`) VALUES
(645, 'f22ce279fd806cc0560d6c8cc9d3b348', 1019, '', '', 1, '12.60', ', '2014-05-14 23:56:19'),
(643, '080e258ccfcb753e843585e0134bf360', 1098, '', '', 1, '14.66', '', '2014-05-14 19:45:48'),
(644, 'f22ce279fd806cc0560d6c8cc9d3b348', 762, '5kg', '', 1, '', '', '2014-05-14 23:56:17'),
(642, '080e258ccfcb753e843585e0134bf360', 762, '5kg', '', 2, '', '', '2014-05-14 19:41:40'),
(641, '080e258ccfcb753e843585e0134bf360', 1019, '', '', 2, '', '', '2014-05-14 19:41:40'),
(640, '6aac7d47725e201993968e4598fccb79', 762, '5kg', '', 1, '', '', '2014-05-12 04:11:28'),
(639, '6aac7d47725e201993968e4598fccb79', 1019, '', '', 1, '', '', '2014-05-12 04:11:28'),
(637, '55de36e79597ff74b1d54d84a8fde49b', 762, '5kg', '', 1, '', '', '2014-05-06 11:07:59');

Open in new window

0
Comment
Question by:sabecs
[X]
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
  • 3
  • 2
5 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 40520156
are you trying to update one table's element with data from another?

Or are you looking at a join query to report .......
0
 

Author Comment

by:sabecs
ID: 40520629
Hi, I am trying to update one table's element with data from another.
0
 
LVL 79

Accepted Solution

by:
arnold earned 500 total points
ID: 40520639
you can do

update cart set price=items.price where cart.items_id=items.id

copy the two tables and test first to make sure the results is what you want.

I am uncertain why you would like the price be part of the cart rather given you have a separate table.
When the price is separate you can account for price changes over time as well as make "sale prices" and other such options available.
i.e. the price displayed will vary by promotions, deals you might offer.

When you have data in different tables, you run into the issue you have where you have to maintain the data in multiple table, while using the cart with a reference to the items_id will mean you only need to maintain the product's price in one location.

Is cart the price the buyer will be paying? in this case, the manner in which you populate the cart information might need to be changed/updated.
0
 

Author Comment

by:sabecs
ID: 40520723
Thanks Arnold, very much appreciated.
I am using the cart table to contain the actual price paid and this becomes part of the order.  The cookieId in the cart table becomes my order number which I then join the orders table. The reason I have done this is that the items table can contain multiple prices depending on option chosen for that product (Size, weight etc.).  I hope this makes sense.
0
 
LVL 79

Expert Comment

by:arnold
ID: 40521024
It does, but the way to populate the cart table is at the time of the order using the join to build the set of data with the insert into directive or if you use an intermediary php etc processing is to assemble the data.

It seems that you are building the table cart which often depending on approach would have had a Tim limited reference that can be used to get to the order which in turn Will be traced to the item that make up that order, ......

Glad I could help.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 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