Solved

join not working!!

Posted on 2004-04-02
6
132 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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