473,231 Members | 1,340 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

sql query update

Hi

i need help with formulating a query that will update the a field on one
table depending on the values from another for example

i have a cart table: cartid, buyerid, productid, quantity

i have a product table: productid, quantity

i want to do a:

select * from cart where buyerid=x

and then depending on the returned rows i want to update the product table:

update product set quantity = quantity - cart.quantity where
productid=cart.productid

if you see what i mean?

Any help appreciated

Kal
Nov 23 '05 #1
11 1921
UPDATE product
SET quantity = quantity -
(SELECT SUM(quantity)
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid)
WHERE EXISTS
(SELECT *
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid) ;

--
David Portas
SQL Server MVP
--

Nov 23 '05 #2
Hi Kal,
You sure should handle the situation that the thing you are
substracting runs out of stocks, but the query should be:

UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid
HTH, Jens Suessmeyer.

Nov 23 '05 #3
>
UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid


Hi

This looked like what i wanted but it gives an error
"missing operator" FROM...

Thank

Kal
Nov 23 '05 #4
Jens wrote:
Hi Kal,
You sure should handle the situation that the thing you are
substracting runs out of stocks, but the query should be:

UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid
HTH, Jens Suessmeyer.


Jens,
I think diablo will want to add "WHERE c.buyerid=x" to your query. Even
then the quantity value will not be correctly updated unless
(buyerid,productid) is unique, which it may be, although that wasn't
explicitly stated.

Diablo,
Please include DDL with future posts (CREATE TABLE, with keys and
constraints) then we don't have to makes guesses about your tables.

--
David Portas
SQL Server MVP
--

Nov 23 '05 #5
> > You sure should handle the situation that the thing you are
substracting runs out of stocks, but the query should be:

UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid

Jens,
I think diablo will want to add "WHERE c.buyerid=x" to your query. Even
then the quantity value will not be correctly updated unless
(buyerid,productid) is unique, which it may be, although that wasn't
explicitly stated.


Yes - Sorry that is correct i will be adding that and buyerid is unique :-)
Diablo,
Please include DDL with future posts (CREATE TABLE, with keys and
constraints) then we don't have to makes guesses about your tables.


OK will do.

here is the query

UPDATE product_t
SET pstock = pstock - c.quantity
FROM product_t p
INNER JOIN cart_t c
ON p.productid=c.productid
where c.buyerid=7
However the issue still remains that Jens code gives a syntax error
missing operator: 'quantity - c.quantity FROM product p ...'

thanks for help

Kal

Nov 23 '05 #6
The following works for me. Notice the key on Cart. Jens' UPDATE only
makes sense if the productid is unique for the given buyerid -
otherwise the total deducted from Product won't match the total of the
corresponding rows in Cart.

CREATE TABLE product (productid INTEGER NOT NULL, quantity INTEGER NOT
NULL /* PRIMARY KEY not specified */) ;
CREATE TABLE cart (buyerid INTEGER NOT NULL, productid INTEGER NOT
NULL, quantity INTEGER NOT NULL, /* ?? */ PRIMARY KEY
(buyerid,productid)) ;

INSERT INTO product (productid, quantity)
VALUES (1,100) ;

INSERT INTO cart (buyerid, productid, quantity)
VALUES (1,1,90) ;

UPDATE P
SET quantity = P.quantity - C.quantity
FROM product P
INNER JOIN Cart C
ON p.productid = c.productid
WHERE C.buyerid = 1 ;

SELECT * FROM product ;

Result:

productid quantity
1 10

(1 row(s) affected)

--
David Portas
SQL Server MVP
--

Nov 23 '05 #7
You sure should handle the situation that the thing you are
substracting runs out of stocks, but the query should be:

UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid
HTH, Jens Suessmeyer.


Jens,
I think diablo will want to add "WHERE c.buyerid=x" to your query. Even
then the quantity value will not be correctly updated unless
(buyerid,productid) is unique, which it may be, although that wasn't
explicitly stated.

ooops sorry buyerid is NOT unique

Cart table
CartID - Unique
BuyerID - Integer
ProductID - Integer
Quantity - Integer

Product table
ProductID - Unique
PStock - Integer
The cart table will have mutiple entries for the same buyerid. so select
'where buyerid=7' returns 3 rows from Cart table.

Sorry for the confusion

Kal
Nov 23 '05 #8
See the UPDATE statements I posted. There's no requirement that buyerid
be unique.

--
David Portas
SQL Server MVP
--

Nov 23 '05 #9
diablo (di****@noplace.com) writes:
However the issue still remains that Jens code gives a syntax error
missing operator: 'quantity - c.quantity FROM product p ...'


Then use the syntax David posted first:

UPDATE product
SET quantity = quantity -
(SELECT SUM(quantity)
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid)
WHERE EXISTS
(SELECT *
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid) ;
The syntax Jens posted works on Microsoft SQL Server, which is what we
discuss in this newsgroup. But judging from the error message you are
using some other product, unknown which. You may getter better help
if you ask in a forum for that product.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #10

Thanks the originnal one worked fine.

I am using Access 2000

Kal
Nov 23 '05 #11
diablo wrote:
Thanks the originnal one worked fine.

I am using Access 2000

Kal


Access is not SQL Server. Ask in the right group in future.

--
David Portas
SQL Server MVP
--

Nov 23 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
3
by: Nick Truscott | last post by:
<? // scoreinput.php - input a match score when match selected from list ?> <html> <head> <basefont face="Verdana"> </head> <body>
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
27
by: Bob | last post by:
running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.