asked on

sql - update statement with variable parameters - how?

I've got my insert statement working recursively, but I don't know how to write the update statement to work in the same way. The way it is now just complains about being invalid.

Can anyone help re-write the statement so it works the same way as the insert above it, using string variables?

its an access database fyi

<%
  'variables
  dim MM_editCmd2, Connstring, Sql
  dim strcustomer_id, strtitle, strbookID, strsales_amnt

Set MM_editCmd2 = Server.CreateObject("ADODB.Connection")

' assign variables to items in the forms collection.
 
  'database connection
  'MM_editCmd2.provider = provider
  MM_editCmd2.ConnectionString = MM_conn_STRING
  MM_editCmd2.Open
  
While (NOT WA_eCart_EOF(bookshop))
'Sql statement to insert the data
 strcustomer_id = identityValue
  strtitle = cStr(WA_eCart_DisplayInfo(bookshop, "Name"))
  strbook_ID = cStr(WA_eCart_DisplayInfo(bookshop, "ID"))
  strsales_amnt = cStr(WA_eCart_DisplayInfo(bookshop, "Price"))
  
  Sql="INSERT INTO Sales (customer_id, title, bookID, sales_amnt) VALUES ('" _
  			& strcustomer_id & "','" & strtitle & "','" _ 
				& strbook_ID & "','" & strsales_amnt & "')"
' execute it
  MM_editCmd2.Execute(Sql)
set bookshop = WA_eCart_MoveNext(bookshop)
wend
set bookshop = WA_eCart_MoveFirst(bookshop)
MM_editCmd2.Close
%>

<%'update stock 
'variables
Dim MM_updateStock, strquantity, Sql2
Set MM_updateStock = Server.CreateObject ("ADODB.Connection")
MM_updateStock.ConnectionString = MM_conn_STRING
MM_updateStock.Open
While (NOT WA_eCart_EOF(bookshop))
Sql statement to insert the data
strquantity = cStr(WA_eCart_DisplayInfo(bookshop, "quantity"))
strbookID = cStr(WA_eCart_DisplayInfo(bookshop, "ID"))
  
Sql2="UPDATE Stock set quantity = (quantity - 1) WHERE EXISTS (SELECT bookID FROM Sales WHERE Sales.bookID = Stock.bookID) VALUES ('" _& strquantity & "','" & strbookID & "')"
  
execute it
  'MM_updateStock.Execute(Sql2)
set bookshop = WA_eCart_MoveNext(bookshop)
wend
set bookshop = WA_eCart_MoveFirst(bookshop)

Open in new window

8080_Diver

What database and what version? (Yes, it does make a difference. ;-)

dohdohdoh

ASKER

access 2007?

ralmada

your update is incorrect. drop the VALUES there. See below:

...
Sql2="UPDATE Stock set quantity = (quantity - 1) WHERE EXISTS (SELECT 1 FROM Sales WHERE Sales.bookID = Stock.bookID)"
...

Open in new window

dohdohdoh

ASKER

Afraid that won't do what I need. I wouldn't be updating *just* those record/s that have just changed, it would go through and compare all books that have ever been sold and deduct stock levels.

The statement needs to use the cStr(WA_eCart_DisplayInfo(bookshop, "ID")) collected from the cart so it can move through just cart items and then stop.

Jez Walters

How are you determining the records that have just changed?

Jez Walters

By the way, it would make sense to reuse some of your variables, rather than declaring and initialising new ones to perform similar operations each time you need them.

Jez Walters

Scratch that last comment - I missed a delimiter! :-)

dohdohdoh

ASKER

Excuse me if I use the wrong terms here, I'm making this up as I go along. It started out as a webassist eCart before I realised it didn't suit my purposes (nice way of saying it) but was too far gone to start again. So when I refer to 'bookshop' its a global variable class type of thing, with functions and procedures to call.

cStr(WA_eCart_DisplayInfo(bookshop, "ID"))

Is a function to show what is in the cart, and I can move through the cart items by telling it to set bookshop = WA_eCart_MoveNext(bookshop), and loop the sql.

That's why I tried to UPDATE the way I did, I want it to look at this ID variable, match it up, decrement, and move on.

Is there a round about way of doing it using a delete and insert or some such then?

ralmada

>>That's why I tried to UPDATE the way I did, I want it to look at this ID variable, match it up, decrement, and move on.<<
The syntax for UPDATE is
UPDATE yourtable set yourcolumn = something
You cannot use VALUES there as you originally did. If strquantity and strbookID have any meanings, please indicate so. Probably you want to use a WHERE clause to limit the UPDATE to certain records only. BUT DEFINITIVELY you CANNOT use VALUES with UPDATE. VALUES is reserved for INSERT only.

dohdohdoh

ASKER

Thanks Sage, I got that.

strquantity is how many copies of a book they ordered, strbookID is the unique ID of the books being purchased. I can't leave them out of the sql, they must be referenced somewhere.

If you know how to phrase the where so it refers to these variables, please advise.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dohdohdoh

ASKER

UPDATE Stock SET quantity = (quantity -1) WHERE EXISTS (SELECT 1 FROM Sales WHERE Sales.bookID = Stock.bookID AND bookID = " & strbookID & ")"

Worked great, just what I was looking for.