Updating multiple rows in SQL

[dfricke10]

I have a list control that lists order items. I want to update these rows directly in the SQL database. They all have a common field in them called 'LinkID'. The field entry will be identical for all of the ones that I want to update. I can do an INSERT with no problem to an SQL database. The only thing I'm stuck on is how to loop through the records to update the 'isSold' field to true or 1. I know the value to LinkID so don't need to hunt for that.

Can someone point me in the right direction?

[dfricke10]

Re: Updating multiple rows in SQL

I think I said this wrong. Let's try again.

I have a table with products. once they're sold, I want to update isSold to true. The products table is PRODUCT_INFORMATION.
The OrderItems table is the orders where the records are essentially these products with a field ORDER_ID which contains the order ID number.
So, the Order_ID is going to be the same for the records in the OrderItems table.
So, I need to call all the records in the Order_ID table with the same Order_ID field (say 21) and then change the isSold to true (or 1).

[madtowng]

Re: Updating multiple rows in SQL

Hi David,

I did the best I could based on the information provided.
I think the code is generic enough that it should work withMySQL or msSQL.
Somewhere along the line it sounds like this is a great time to use a trigger to update the product_information table.

Gregg

update product_information p
left join OrderItems  on p.order_id = o.order_id
set p.isSold = 1
where (p.isSold <> 1 or p.isSold is null) and p.order_id = o.order_id

[dfricke10]

Re: Updating multiple rows in SQL

That's almost there, but not quite.

The only problem is, the order_id doesn't exist in the Product_Information table.

Think of Product_Information as the inventory table.
OrderItems is the table where the things they're buying are stored.
OnlineOrders is the order record.

After the order, the OrderItems table records are update with the OrderID from OrderItems. There's already another linking field called CartLinkID which was used all along to keep track of what is in the customer's cart. It's also a session id so it can easily be writted to the OnlineOrders table.

Now I need to update the inventory to sold. The Product_Information ID numbers are stored in the OrderItems table under PARTNO field. So, somehow I need to call the OrderItems table, find the corresponding items that belong to OnlineOrders record, then cross reference to Product_Information and mark them sold.


I sometimes talk too much when I explain, so let me know if this makes sense.

[madtowng]

Re: Updating multiple rows in SQL

The part about order_id not being in product_information makes sense.
If I have this right, the orderItems table should have an orderID and productID column, and then OnlineOrders should have an onlineOrderID and orderID column.
Assuming a value in the OnlineOrders table has to change to trigger this (like a purchaseTime column), the code below "should" work.

I have a much better time if I have sample tables/columns to work with. I mentioned the purchaseTime column because this screams to be done via trigger.
I'm attaching an image of what I'm guessing is the basic table setup. Feel free to let me know how I did.
2020-03-19_1-38-13.png

update product_information p
left join OrderItems i on i.fkProductID = p.ID
left join OnlineOrder o on o.fkOrderItemsID = i.ID
set p.isSold = 1
where (p.isSold <> 1 or p.isSold is null) and p.id = i.fkProductID and o.fkOrderItemsID = i.ID

[dfricke10]

Re: Updating multiple rows in SQL

Attached is how it works.

The OrderItems holds the Product_Information_ID AND the OnlineOrders ID. That's so there can be more than 1 OrderItem. Imagine a shopping cart.

I'm gonna play with what you have and see if I can modify to work. Thanks!!

GRAPHICS-0140.png

[madtowng]

Re: Updating multiple rows in SQL

That pic helped a lot.
This should do it.

update product_information p
left join OrderItems i on i.fkProductID = p.ID
left join OnlineOrder o on o.ID = i.onlineordersID
set p.isSold = 1
where (p.isSold <> 1 or p.isSold is null) and p.id = i.fkProductID and o.ID = i.onlineordersID

[dfricke10]

Re: Updating multiple rows in SQL

What am I missing here. it's not working.

dim orderid as c
orderid = 34
args.add("ORDERINFOID",orderid)

UPDATE Product_Information p
LEFT JOIN OrderItems i on i.PARTNO = p.PRODUCT_INFORMATION_ID
LEFT JOIN OnlineOrder o on o.Order_ID = i.ORDERINFO_ID
SET p.isSold = 1
where (p.isSold <> 1 or p.isSold is null) and p.PRODUCT_INFORMATION_ID = i.PARTNO and o.Order_id = :ORDERINFOID

[madtowng]

Re: Updating multiple rows in SQL

Please show the code, and hopefully the error text.
If you want to talk this through, my phone is 6086283558

[dfricke10]

Re: Updating multiple rows in SQL

Is this what you're looking for? Should work, huh?

dim cn as sql::connection
dim flag as l 
flag = cn.open("::Name::SQL")
if flag = .f. then 
	'there was an error
	dim errorText as c 
	errorText = cn.callresult.text
	end
end if 
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c 
sql = <<%sql%
UPDATE Product_Information p
LEFT JOIN OrderItems i on i.PARTNO = p.PRODUCT_INFORMATION_ID
LEFT JOIN OnlineOrder o on o.Order_ID = i.ORDERINFO_ID
SET p.isSold = 1
where (p.isSold <> 1 or p.isSold is null) and p.PRODUCT_INFORMATION_ID = i.PARTNO and o.Order_id = :ORDERINFOID
%sql%
dim args as sql::arguments

dim orderid as c
orderid=34
args.add("ORDERINFOID",orderid)

'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then 
	'there was an error
	dim errorText as c 
	errorText = cn.callresult.text
	cn.close()
	end
end if 
dim rowsUpdated as n 
'get the number of rows that were updated
rowsUpdated = cn.CallResult.rowsaffected
cn.close()

[madtowng]

Re: Updating multiple rows in SQL

Hurray for stupid things(Alpha's portable SQL determines valid sql code is invalid) :)
It's not your fault, but change cn.PortableSQLEnabled = .t. to cn.PortableSQLEnabled = .f.

[dfricke10]

Re: Updating multiple rows in SQL

I've tried this format every which way I can think of and can't get it to work. What I assume it's doing is a backwards path through the databases to get the product ID and update it to sold.

This doesn't work either. And pick an SQL format and I've tried it. Argh...

dim cn as sql::connection
dim flag as l 
flag = cn.open("::Name::SQL")
if flag = .f. then 
	'there was an error
	dim errorText as c 
	errorText = cn.callresult.text
	end
end if 

'turn on portable SQL
cn.PortableSQLEnabled = .f.
dim SQL as c 
sql = <<%sql%
UPDATE
  Product_Information
  LEFT JOIN OrderItems ON OrderItems.PARTNO = Product_Information.PRODUCT_INFORMATION_ID
  LEFT JOIN OnlineOrders ON OnlineOrders.Order_ID = OrderItems.ORDERINFO_ID
SET
  Product_Information.isSold = 1
WHERE
  Product_Information.PRODUCT_INFORMATION_ID = OrderItems.PARTNO
  AND OnlineOrders.Order_ID = 37
%sql%
dim args as sql::arguments

dim orderid as n
orderid = 37
args.add("ORDERINFOID",orderid)
'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then 
	'there was an error
	dim errorText as c 
	errorText = cn.callresult.text
	cn.close()
	end
end if 
dim rowsUpdated as n 
'get the number of rows that were updated
rowsUpdated = cn.CallResult.rowsaffected
cn.close()

[madtowng]

Re: Updating multiple rows in SQL

What is the error?

[Steve Workings]

Re: Updating multiple rows in SQL

I've not studied your query much, but wanted to suggest a different approach.

Instead of those joins for the table being updated, you can get a list of keys that need updating like this:

UPDATE MyTable
SET SomeValue = 1
WHERE ID in(
	(SELECT something here that returns the list of MyTable.IDs that need updating)
	)

And, yes, do not turn on portableSQL for this stuff

[dfricke10]

Re: Updating multiple rows in SQL

Gregg, there's no error. Just doesn't update. If I take away all the JOINs and update the isSold by itself, it works. But obviously we can't do that because we don't know the product numbers.