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

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

Update query syntax

Found examples on EE, and built my own update query, but cant figure why it wont work.

Updateing table "tblStock" from a select query "qry StkInDetails Grouped"


**ACCESS 2007
UPDATE ts 
SET ts.pc = tn.pcs
FROM tblStock AS ts 
 
INNER JOIN 
[qry StkInDetails Grouped] AS tn 
ON (ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location)
WHERE (((ts.pkg)=0))

Open in new window

0
CMDAI
Asked:
CMDAI
  • 10
  • 3
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this small modif:
UPDATE ts 
SET ts.pc = tn.pcs
FROM tblStock AS ts 
INNER JOIN [qry StkInDetails Grouped] AS tn 
ON ((ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location))
WHERE (ts.pkg=0)

Open in new window

0
 
CMDAIAuthor Commented:
i get the same error
pic.jpg
0
 
CMDAIAuthor Commented:
access highlights the work FROM
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CMDAIAuthor Commented:
access highlights the word FROM
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, in ms access the syntax is indeed a bit different than ms sql server:
UPDATE tblStock AS ts 
INNER JOIN [qry StkInDetails Grouped] AS tn 
ON ((ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location)) 
SET ts.pc = tn.pcs
WHERE (ts.pkg=0)

Open in new window

0
 
CMDAIAuthor Commented:
ACCESS : Operation Must be an updatable Query.
0
 
CMDAIAuthor Commented:
I cannot update table from Query? :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have primary keys on the table(s)?
which columns?
0
 
CMDAIAuthor Commented:
"tblStock" has 'ID'
but
"qry StkInDetails Grouped" doesnt have , its a Union, Select Query
0
 
Rey Obrero (Capricorn1)Commented:
how many tables were used in [qry StkInDetails Grouped] ?
3 or more?

does the query uses aggregate functions i.e., sum,average, etc ?
0
 
CMDAIAuthor Commented:
The table and the query
pic2.jpg
0
 
Rey Obrero (Capricorn1)Commented:
0
 
CMDAIAuthor Commented:
I nee to get back home, will get back here in about 15 min

0
 
bmatumburaCommented:
Try the following code... you can't use an alias as the table being updated:
UPDATE tblStock 
SET pc = tn.pcs
FROM tblStock AS ts 
INNER JOIN [qry StkInDetails Grouped] AS tn 
ON ((ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location))
WHERE (ts.pkg=0)

Open in new window

0
 
bmatumburaCommented:
You may have to include the WHERE part in the INNER JOIN like so:
UPDATE tblStock 
SET pc = tn.pcs
FROM tblStock AS ts 
INNER JOIN [qry StkInDetails Grouped] AS tn 
ON ((ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location)
AND (ts.pkg = 0))

Open in new window

0
 
CMDAIAuthor Commented:
OK thank's this code worked, and i worked around
The Union, with VB (create , Update , Delete Table)
UPDATE tblStock AS ts 
INNER JOIN [qry StkInDetails Grouped] AS tn 
ON ((ts.pkg = tn.pkgs) 
AND (ts.PartNo = tn.PartNo) 
AND (ts.Brand = tn.Brand) 
AND (ts.Category = tn.Category) 
AND (ts.Location = tn.Location)) 
SET ts.pc = tn.pcs
WHERE (ts.pkg=0)

Open in new window

0
 
CMDAIAuthor Commented:
Thank's again
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 10
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now