Solved

MySQL - get data from one table to another

Posted on 2014-12-27
5
253 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
  • 3
  • 2
5 Comments
 
LVL 77

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 77

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 77

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
generate a dynamic mysql query 2 41
mySql Syntax 7 44
simple mysql statement 3 32
MySQL  on Tomcat 8 30
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now