Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

join not working!!

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
finnstone
Asked:
finnstone
3 Solutions
 
jayeshshahCommented:
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
 
hartCommented:
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
 
mrichmonCommented:
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
hartCommented:
finnstone : did it work out :-)
0
 
PE_CF_DEVCommented:
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
 
mrichmonCommented:
finnstone,

Did any of these solutions work for you?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now