Solved

join not working!!

Posted on 2004-04-02
6
131 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

734 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