help with subquery

[BLTibbs]

Help!!
Here is the query from he SQL pane that pulls up just the lowest priced of
each competitor. How do I update another row from this "lowest priced"
record called [condition] into a table called "BookTbl" when the two can be
matched to a field called [ISBN]?

What am I missing here?

SELECT TempCompetitorTbl.ISBN, Min(TempCompetitorTbl.SellerPrice) AS
MinOfSellerPrice, Count(TempCompetitorTbl.KeyID) AS CountOfKeyID
FROM TempCompetitorTbl
GROUP BY TempCompetitorTbl.ISBN, TempCompetitorTbl.SellerCondition
HAVING (((TempCompetitorTbl.SellerCondition) Like "used"));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL-92 it'd be done like this:

UPDATE BookTbl
SET column_name = (SELECT Min(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = BookTbl.ISBN
AND SellerCondition = "used")

Unfortunately, JET (the Access DB engine) won't accept that - you'll get
a "Must be updateable query" error. So, you have to use the DMIN()
function:

UPDATE BookTbl
SET column_name = DMIN("SellerPrice","TempCompetitorTbl",
"ISBN=" & BookTbl.ISBN & " AND
SellerCondition = 'used'")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+unVYechKqOuFEgEQJKOgCg/NWJmDy+s1Mhtlf6oZembJMkyw0AoPnF
I5+51mrIq1eG3gDXKPm/1XDD
=a8l5
-----END PGP SIGNATURE-----

 

[BLTibbs]

Here is the SQL I have so far, but it is trying tupdate all of the records in
"booktbl" instead of just the matches of the isbn field (which in booktbl is
called product-id. And when the query finishes, it says that non of the
records can be updated because of a type mismatch.

UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" & BookTbl.[product-id] & "AND
[SellerCondition] = 'used'")

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL-92 it'd be done like this:

UPDATE BookTbl
SET column_name = (SELECT Min(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = BookTbl.ISBN
AND SellerCondition = "used")

Unfortunately, JET (the Access DB engine) won't accept that - you'll get
a "Must be updateable query" error. So, you have to use the DMIN()
function:

UPDATE BookTbl
SET column_name = DMIN("SellerPrice","TempCompetitorTbl",
"ISBN=" & BookTbl.ISBN & " AND
SellerCondition = 'used'")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+unVYechKqOuFEgEQJKOgCg/NWJmDy+s1Mhtlf6oZembJMkyw0AoPnF
I5+51mrIq1eG3gDXKPm/1XDD
=a8l5
-----END PGP SIGNATURE-----

Help!!
Here is the query from he SQL pane that pulls up just the lowest priced of
each competitor. How do I update another row from this "lowest priced"
record called [condition] into a table called "BookTbl" when the two can be
matched to a field called [ISBN]?

What am I missing here?

SELECT TempCompetitorTbl.ISBN, Min(TempCompetitorTbl.SellerPrice) AS
MinOfSellerPrice, Count(TempCompetitorTbl.KeyID) AS CountOfKeyID
FROM TempCompetitorTbl
GROUP BY TempCompetitorTbl.ISBN, TempCompetitorTbl.SellerCondition
HAVING (((TempCompetitorTbl.SellerCondition) Like "used"));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A different way:

UPDATE BookTbl As B INNER JOIN TempCompetitorTbl As T
ON B.[product-id] = T.ISBN
SET B.LowUsedSellerPrice = T.SellerPrice
WHERE T.SellerPrice =
(SELECT MIN(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = B.[product-id] AND SellerCondition = 'used')

If you get the same error (type mismatch) then please show the data
types for each column in each table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+zfE4echKqOuFEgEQICWwCg24alcmNDyW+Abq/2da7fE/HiUzUAoOw5
pyCFQlGaE8efHiCz9pYrAjsw
=qFud
-----END PGP SIGNATURE-----

Here is the SQL I have so far, but it is trying tupdate all of the records in
"booktbl" instead of just the matches of the isbn field (which in booktbl is
called product-id. And when the query finishes, it says that non of the
records can be updated because of a type mismatch.

UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" & BookTbl.[product-id] & "AND
[SellerCondition] = 'used'")



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL-92 it'd be done like this:

UPDATE BookTbl
SET column_name = (SELECT Min(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = BookTbl.ISBN
AND SellerCondition = "used")

Unfortunately, JET (the Access DB engine) won't accept that - you'll get
a "Must be updateable query" error. So, you have to use the DMIN()
function:

UPDATE BookTbl
SET column_name = DMIN("SellerPrice","TempCompetitorTbl",
"ISBN=" & BookTbl.ISBN & " AND
SellerCondition = 'used'")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+unVYechKqOuFEgEQJKOgCg/NWJmDy+s1Mhtlf6oZembJMkyw0AoPnF
I5+51mrIq1eG3gDXKPm/1XDD
=a8l5
-----END PGP SIGNATURE-----

Help!!
Here is the query from he SQL pane that pulls up just the lowest priced of
each competitor. How do I update another row from this "lowest priced"
record called [condition] into a table called "BookTbl" when the two can be
matched to a field called [ISBN]?

What am I missing here?

SELECT TempCompetitorTbl.ISBN, Min(TempCompetitorTbl.SellerPrice) AS
MinOfSellerPrice, Count(TempCompetitorTbl.KeyID) AS CountOfKeyID
FROM TempCompetitorTbl
GROUP BY TempCompetitorTbl.ISBN, TempCompetitorTbl.SellerCondition
HAVING (((TempCompetitorTbl.SellerCondition) Like "used"));

 

[BLTibbs]

Hallelujah! Thanks - that worked.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A different way:

UPDATE BookTbl As B INNER JOIN TempCompetitorTbl As T
ON B.[product-id] = T.ISBN
SET B.LowUsedSellerPrice = T.SellerPrice
WHERE T.SellerPrice =
(SELECT MIN(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = B.[product-id] AND SellerCondition = 'used')

If you get the same error (type mismatch) then please show the data
types for each column in each table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+zfE4echKqOuFEgEQICWwCg24alcmNDyW+Abq/2da7fE/HiUzUAoOw5
pyCFQlGaE8efHiCz9pYrAjsw
=qFud
-----END PGP SIGNATURE-----

Here is the SQL I have so far, but it is trying tupdate all of the records in
"booktbl" instead of just the matches of the isbn field (which in booktbl is
called product-id. And when the query finishes, it says that non of the
records can be updated because of a type mismatch.

UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" & BookTbl.[product-id] & "AND
[SellerCondition] = 'used'")



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL-92 it'd be done like this:

UPDATE BookTbl
SET column_name = (SELECT Min(SellerPrice) FROM TempCompetitorTbl
WHERE ISBN = BookTbl.ISBN
AND SellerCondition = "used")

Unfortunately, JET (the Access DB engine) won't accept that - you'll get
a "Must be updateable query" error. So, you have to use the DMIN()
function:

UPDATE BookTbl
SET column_name = DMIN("SellerPrice","TempCompetitorTbl",
"ISBN=" & BookTbl.ISBN & " AND
SellerCondition = 'used'")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+unVYechKqOuFEgEQJKOgCg/NWJmDy+s1Mhtlf6oZembJMkyw0AoPnF
I5+51mrIq1eG3gDXKPm/1XDD
=a8l5
-----END PGP SIGNATURE-----

BLTibbs wrote:

Help!!
Here is the query from he SQL pane that pulls up just the lowest priced of
each competitor. How do I update another row from this "lowest priced"
record called [condition] into a table called "BookTbl" when the two can be
matched to a field called [ISBN]?

What am I missing here?

SELECT TempCompetitorTbl.ISBN, Min(TempCompetitorTbl.SellerPrice) AS
MinOfSellerPrice, Count(TempCompetitorTbl.KeyID) AS CountOfKeyID
FROM TempCompetitorTbl
GROUP BY TempCompetitorTbl.ISBN, TempCompetitorTbl.SellerCondition
HAVING (((TempCompetitorTbl.SellerCondition) Like "used"));