Solved

MySQL - get data from one table to another

Posted on 2014-12-27
5
254 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how do i form the query to get different columns total count 13 48
category table 2 24
sort in mysql based off of query param 4 24
AWS EC2 & RDS Instance 5 34
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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