• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Query Syntax

This works..

SELECT pcac
FROM pcac pc
JOIN
      (SELECT MAX(pcac_id) AS pcac_id
      FROM pcac
      WHERE deleted = 'N'
      GROUP BY estdetail_id
      ORDER BY estdetail_id ASC
      ) AS pc2 ON pc2.pcac_id = pc.pcac_id
ORDER BY estdetail_id ASC;

Now I'm trying to update those results to another table. This doesn't work.
Error Code : 1054
Unknown column 'd.estdetail_id' in 'where clause'

UPDATE estdetail d,
      (
      SELECT pcac AS samount
      FROM pcac pc
      WHERE pc.estdetail_id = d.estdetail_id
      AND d.deleted = 'N'
      ORDER BY pcac_id
      DESC LIMIT 1
      ) z
SET d.PCAC = z.samount
0
hdcowboyaz
Asked:
hdcowboyaz
  • 6
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl...

UPDATE d
SET d.PCAC = z.samount
FROM estdetail d
   JOIN (      
      SELECT pcac AS samount
      FROM pcac pc
      WHERE pc.estdetail_id = d.estdetail_id
      AND d.deleted = 'N'
      ORDER BY pcac_id
      DESC LIMIT 1
      ) z ON d.pcac_id = z.pcac_id
0
 
hdcowboyazAuthor Commented:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM estdetail d
   JOIN (      
      SELECT pcac AS samount
      FROM pcac pc' at line 3
0
 
hnasrCommented:
List few records and their output.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hdcowboyazAuthor Commented:
There is only one output (field) pcac.  Its just a number?
0
 
hnasrCommented:
Input records of table to update.

The value to update with and where does it come from.

Output records of table updated.
0
 
hdcowboyazAuthor Commented:
I beleive this it it...

UPDATE estdetail d,
      (
      SELECT pc.estdetail_id, pc.pcac AS samount
      FROM estdetail d, pcac pc
      WHERE d.estdetail_id = pc.estdetail_id
      AND d.deleted = 'N'
      AND pc.deleted = 'N'
      ORDER BY pcac_id DESC
      ) z
SET d.PCAC = z.samount
WHERE z.estdetail_id = d.estdetail_id;
0
 
hnasrCommented:
Sorry hdcowboyaz,

I mean if I have table a (id, f1, f2)

Input:
id  f1  f2
1  2   3
2  2   4
3  3   5

Process: replace values of field f1 with 5 if current value is 2
5 is comming from table b(i, j) which is the maximum value of j where i=2
i  j
1  2
1  3
2  3
2  5 - this is the value to update with

Output:
id  f1  f2
1  5   3 - this record updated
2  5   4 - this record updated
3  3   5

This will help finding the process, which is the required query.
0
 
hdcowboyazAuthor Commented:
CREATE TABLE `estdetail` (
  `estdetail_id` MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `est_id` MEDIUMINT(10) UNSIGNED DEFAULT NULL,
  `PCAC` DECIMAL(11,2) DEFAULT '0.00',
  `deleted` ENUM('Y','N') DEFAULT 'N',
  PRIMARY KEY (`estdetail_id`),
) ENGINE=MYISAM AUTO_INCREMENT DEFAULT CHARSET=latin1


CREATE TABLE `pcac` (
  `pcac_id` MEDIUMINT(10) NOT NULL AUTO_INCREMENT,
  `estdetail_id` MEDIUMINT(10) UNSIGNED NOT NULL DEFAULT '0',
  `pcac` DECIMAL(11,2) DEFAULT '0.00',
  `deleted` ENUM('Y','N') DEFAULT 'N',
  PRIMARY KEY (`pcac_id`),
) ENGINE=MYISAM AUTO_INCREMENT DEFAULT CHARSET=latin1

The purpose is to update the estdetail table (field = PCAC) from the pcac table (field = pcac). There can be more than one pcac vavles for a given estdetail_id within the pcac table. The pcac wiht the largest pcacc_id is the value to be transtered.
0
 
hdcowboyazAuthor Commented:
please close
0
 
hdcowboyazAuthor Commented:
please close
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.

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