Moving a cell value to another row based on ID numbers-CodePudding

I am looking to move B to the above row. It can either be placed where the Null value is in Column B or another column can be created. The value of B is linked to value A through an ID. The ID for value B is always X 2 (the values in the ID column are integers).

I can’t just move the value up because the table I am working with has thousands of rows. It must be linked to the ID’s.

Please let me know if you have any questions. Any assistance is much appreciated. Thank you.

ID	Column A	Column B
X	A	NULL
X 2	NULL	B

Keep in mind I am very new to SQL. Below is what I tried. It created a new column that only contains NULL values.

Select
   Column_B
From
   Table_Name
Where
   Table_Name.ID = Table_Name.ID  2 ) AS Col_B_Value

CodePudding user response：

You can use a conditional subselect for that

UPDATE Table_Name T1
SET Column_B = (Select
   Column_B
From
   Table_Name
Where
   Table_Name.ID = T1.ID  2 )
WHERE Column_B IS NULL

Some databases could have a problem so you can make

UPDATE Table_Name T1
SET Column_B = (Select
   T2.Column_B
From
   (SELECT ID,Column_B  FROM Table_Name) T2
Where
   T2.ID = T1.ID  2 )
WHERE Column_B IS NULL

CodePudding user response：

You could just do it with 2 updates statements

UPDATE Table
SET Column B = 'B'
WHERE ID = 'X'

UPDATE Table
SET Column B = NULL
WHERE ID = 'X 2'

If you need to do it in a select statement you could do it with a case statement too

SELECT ID,
Column A,
CASE WHEN ID = X AND Column B = NULL THEN 'B'
ELSE Column B END
FROM Table