Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL - get data from one table to another

Posted on 2014-12-27
5
Medium Priority
?
265 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 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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