I have two tables.
- Cart (cartId, prodId, etc)
- Codes (Id, cartId, Code)
Each line in the Cart table has a code from the Codes table.
Because of an error, we now have 20 cart rows (But another time around it could be 1000 rows, so manually does not work all the time..), that each has a wrong code in the Codes table. Therefor I need to update the Codes table, where the cartId is identical.
My question is: How can I select all the records from the Cart table, with lets say prodId 10, and then use that collection to to update all the codes in the Codes table where there is a match on cartId.
Something like (I know this is awfully wrong):
UPDATE Codes (SELECT cartId FROM Cart WHERE prodId = 10) WHERE Codes.cartId = CartId
So this updates incorrect. I need to loop the collection and then update the Codes table where cartId is identical to each other, but I just cant get my head around how to accomplish that.
Any help appreciated :-)