Solved

join not working!!

Posted on 2004-04-02
6
130 Views
Last Modified: 2013-12-24
this simple query is supposed to add records from itemlist table into orderhistory table, can anyone tell me why it does not work anymore now that i have moved the db from access to sql

            <cfquery datasource="#Application.DataSource#" username="x" password="x" name="test3">
      UPDATE itemlist
      LEFT JOIN orderhistory ON itemlist.itemlistoid = orderhistory.itemlistoid
      SET orderhistory.itemlistoid = itemlist.itemlistoid, orderhistory.useroid = itemlist.useroid, orderhistory.cartoid = itemlist.cartoid, orderhistory.itemoid = itemlist.itemoid, orderhistory.itemlistselection = itemlist.itemlistselection, orderhistory.itemquantity = itemlist.itemquantity;
      </cfquery>
0
Comment
Question by:finnstone
[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
6 Comments
 
LVL 5

Expert Comment

by:jayeshshah
ID: 10746558
change the update statement to

UPDATE itemlist
SET orderhistory.itemlistoid = itemlist.itemlistoid, orderhistory.useroid = itemlist.useroid, orderhistory.cartoid = itemlist.cartoid, orderhistory.itemoid = itemlist.itemoid, orderhistory.itemlistselection = itemlist.itemlistselection, orderhistory.itemquantity = itemlist.itemquantity
from itemlist
     LEFT JOIN orderhistory ON itemlist.itemlistoid = orderhistory.itemlistoid


Regards

JBS
0
 
LVL 11

Accepted Solution

by:
hart earned 200 total points
ID: 10746563
i don't see u inserting records, u r updating the table..

just try the following query

insert into orderhistory (itemlistoid, useroid, cartoid, itemoid , itemlistselection , itemquantity)
as select itemlistoid, useroid, cartoid, itemoid , itemlistselection , itemquantity from itemlist

Regards
Hart
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 150 total points
ID: 10758281
Actually you can write it even simpler as :

INSERT INTO orderhistory SELECT itemlistoid, useroid, cartoid, itemoid, itemlistselection, itemquantity FROM itemlist

which will insert all of the columns from itemlist into the orderhistory table
You don't need to specify fields unless there are fields that do not match.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 11

Expert Comment

by:hart
ID: 10774821
finnstone : did it work out :-)
0
 
LVL 6

Assisted Solution

by:PE_CF_DEV
PE_CF_DEV earned 150 total points
ID: 10775051
If you want to update orderhistory then maybe this:

Update orderhistory <!--- Not itemlist --->
SET orderhistory.itemlistoid = itemlist.itemlistoid, orderhistory.useroid = itemlist.useroid, orderhistory.cartoid = itemlist.cartoid, orderhistory.itemoid = itemlist.itemoid, orderhistory.itemlistselection = itemlist.itemlistselection, orderhistory.itemquantity = itemlist.itemquantity
From itemlist  LEFT JOIN orderhistory ON itemlist.itemlistoid = orderhistory.itemlistoid

HTH
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10793550
finnstone,

Did any of these solutions work for you?
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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