ansaurus

Question

I've got to update a column in one SQL table with a counter stored in another table, and update that table too

Answer 1

+3 A:

if you are INSERTING into one table and then UPDATE(ing) the next table you can use @@IDENTITY to give you the auto increment ID from the first table.

E.g.

INSERT INTO table1 (description) VALUES ('blah');
UPDATE table2 SET (tabel1ID = @@IDENTITY) WHERE condition

@@IDENTITY will give you the ID of the last inserted row.

JD 2010-03-17 04:41:33

Thank you, JD. But no, I'm not inserting it. I need to get the "next user ID" from row 1 of Table1, increment and update that column in row 1 of Table1 with "next user ID + 1" and insert that into row 1 of table 2, then repeat for next row of Table 2 until no more rows. But I'm always only updating Row 1 of Table 1.

Bucket 2010-03-17 15:58:54

Can you give a bit more info... e.g. DB Schema then we may be able to help a bit more...

JD 2010-03-18 01:38:31

OK.. so my take on your problem (And let me know if it is wrong):1. You have table1 which has a 'userID' column.2. You want to make 'userID' = userID + 1.3. You then want to insert this new 'userID' into table 2.Do you need to use SQL? This might be easier to implement using a secondary language (such as C# or PHP or your favourite language) in a foreach loop.If you need to use SQL use a 'while begin end' loop...There are plenty of good tutorials on 'foreach' (and in SQL 'while begin end') loops in both SQL and other languages on Google :)Hope this helps a little... Shout if not

JD 2010-03-18 01:52:23

ansaurus

tags:

views:

answers:

I've got to update a column in one SQL table with a counter stored in another table, and update that table too

related questions