Select certain records into another existing table

[klemme]

Hi all,

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 :-)

/Klemme

[klemme]

This example illustrates better what I want to do:

UPDATE Codes SET CartId
    (SELECT cartId FROM Cart WHERE prodId = 10) 
WHERE Codes.batchId = 783;

I might need to create a stored procedure and create a temporay table containing the collection of cartId's, and then loop it and insert it into Codes?

[pritaeas]

You mean something like this?

UPDATE Codes SET Code = 783
WHERE cartId IN (SELECT cartId FROM Cart WHERE prodId = 10)

[klemme]

So this is the final idea, that I cant seem to make happen in real life - I have an unknown number of basket_id's from the basket_prod table, which I need to query. When I have them I need to assign each one of them to a Code in the codes table, where the Codes.batch_id = 782. The codes are predefined, so I just need to insert each basket_id from basket_prod table into the Codes table.

UPDATE codes AS C 
SET basket_id = (
    SELECT basket_id FROM basket_prod WHERE prod_id = 10 -- Can have from 1-1000 records, so need to loop this and update the codes table that has the where clause fulfilled too
)
WHERE C.batch_id = 782 AND C.basket_id IS NOT NULL;

[pritaeas]

It might be better if you give sample data of what you have, and what you need it to be, instead of trying to explain with a query.

[klemme]

basket_prod table: - Contains order rows

basket_id   prod_id  amount
1           10          100
2           10          200
3           10          300
4           10          400
5           10          500
6           10          600
7           10          700

Each of these lines has a code in the Codes table - basket_id is the foreign key, and the identifier in the Codes table is batch_id 783

Codes table

ID  basket_id     code                  batch_id
1   null          unique_code_here      783
2   null            unique_code_here    783
3   null            unique_code_here    783
4   null            unique_code_here    783
5   null            unique_code_here    783
6   null            unique_code_here    783
7   null            unique_code_here    783
8   null          unique_code_here      783
9   null          unique_code_here      783
10  null         unique_code_here       783
11  null         unique_code_here       783
12  null         unique_code_here       783
13  null         unique_code_here       783
14  null         unique_code_here       783
15  null         unique_code_here       783
16  null         unique_code_here       783
17  null         unique_code_here       783
18  null         unique_code_here       783

Codes are predefined, and there can only be sold as many items as we have codes, thats why some are null in basket_id.

So we find out that all codes are wrong, and we reset all basket_id's in the codes table to be = null.

Next we update the Codes table and insert the new valid codes where the batch_id = 783.

Now we need to run through the order rows in basket_prod table. In this example we have 7 sold, and foreach order row in basket_prod with a prod_id = 10, we want to update Codes table with batch_id = 783 and assign the basket_id.

I hope this makes more sense :-)