0

I have a table A with ID's only and another table B with two columns, ID and Product Number. The ID column in table B has nulls and Product Number has Product Numbers. I would like to update table B with the ID's in column in no specific order just so that the Product Number has ID's.

I have tried to use update but that has not worked, have tried insert but it just adds the ID's in A to the bottom of the list in B. Would like to do this in Microsoft SQL.

SQL code tried:

IF OBJECT_ID('tempdb..#ProductNum') IS NOT NULL DROP TABLE #ProductNum

SELECT  ID
INTO    #ProductNum
FROM    Products

    

UPDATE  [ProductCatalogue] PC
SET
PC.ID = Pn.ID
FROM #ProductNum Pn
INNER JOIN 
[ProductCatalogue] PC   
ON Pc.ID = Pn.ID
WHERE Pc.ID IS NULL
jarlh
  • 42,561
  • 8
  • 45
  • 63
BRILLIANT
  • 55
  • 1
  • 5

2 Answers2

0

It sounds a lot like you would be better off having the ID-Column Autoincrement, instead of giving it the IDs from table A. This is already explained in this answer.

In case you actually need the specific IDs from table A, this SO thread might help you.

Chris
  • 180
  • 1
  • 14
  • The ID in this case was just an example but the ID am working with can not be auto incremented as I need to take the ID from a master table. The second option might work, however the the ID's in A have no match in table B as Table B just has the ID column but has NULL's in that column. So i pretty much want to assign the ID's in A to the product numbers in table B – BRILLIANT May 06 '22 at 10:17
0

Solved the issue by creating auto increment columns on each table and called it Row_ID. Then I used Row_ID to join the tables together with some logic provided by @Chris above.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
BRILLIANT
  • 55
  • 1
  • 5