CODEWITHSUNDEEP
sqlsql-servert-sql
sql_query_questioner
sql_query_questioner

Reputation: 15

Update column in SQL Server using update command

I would like to update the original_tbl (url below) to look like the update_tbl (URL below). I am uncertain about the T-SQL for this.

original table: enter image description here

preferred updated table: enter image description here

Upvotes: 0

Views: 92

Answers (4)

John
John

Reputation: 101

Declare a table variable, update the original table using an update + join combo, and then run a delete statement on the original table.
declare @systemIDs table (studentID int, systemID varchar(50))
insert into @systemIDs
select studentID, systemID from original_tbl where systemID is not null
update ot set ot.systemID = si.systemID from original_tbl ot inner join @systemIDs si on ot.studentID = si.studentID
delete from original_tbl where attempts is null

Upvotes: 0

GuidoG
GuidoG

Reputation: 12059

if you have just a few than you can do D-Shih's suggestion
If you have many occurences where the column Attemptsis null, you could first generate all your update commands first. So actually you are using 2 steps

Step one: generate all update commands

select 'update original_table set systemID = ''' + SystemID + ''' where StudentID = ' + convert(varchar, t.studentID)
from   original_table t
where  Attempts is null

step 2: execute all commands

now you get a recordset with all the update commands you need to execute.
Just copy it and execute them all.

And finally delete the empty attempts

delete from original_Table where Attempts is null

Do not forget to check the generated update commands off course if they are correct...

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this:

Data generation:

declare @x table(studentId int, attempts varchar(2), systemId varchar(10))
insert into @x values
(105, '1', ''),
(105, '2', ''),
(105, '3', ''),
(105, '', 'CRU877'),
(106, '1', ''),
(106, '2', ''),
(106, '3', ''),
(106, '4', ''),
(106, '', 'AUR145')

Update query:

update @x set systemId = sysId from (select studentId [stdId], systemId [sysId] from @x where attempts = '') [x] where studentId = stdId
delete @x where attempts = ''
select * from @x

Pictures you attached are pretty ambigious, I don't know whether blank cells are NULLs or empty strings. Just in case there's solution considering this:

declare @x table(studentId int, attempts int, systemId varchar(10))
insert into @x values
(105, 1, null),
(105, 2, null),
(105, 3, null),
(105, null, 'CRU877'),
(106, 1, null),
(106, 2, null),
(106, 3, null),
(106, 4, null),
(106, null, 'AUR145')

update @x set systemId = sysId from (select studentId [stdId], systemId [sysId] from @x where attempts is null) [x] where studentId = stdId
delete @x where attempts is null
select * from @x

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

I assume you have additional systemId's in your table so you can do it for the entire table in one update

    DECLARE @systemId NVARCHAR(10)
    DECLARE @tb table (StudentId int , attempts int , systemId nvarchar(10))
    INSERT INTO @tb 
    VALUES (105,0,'CRU877'),
           (105,1,NULL),
           (105,2,NULL),
           (105,3,NULL),
           (106,0,'AUR145'),
           (106,1,NULL),
           (106,2,NULL),
           (106,3,NULL),
           (106,4,NULL)

    /*Before*/
    SELECT * 
    FROM @tb

    UPDATE @tb 
    SET @systemId = systemId = CASE WHEN systemId IS NULL THEN @systemId ELSE systemId END

    /*After*/
    SELECT * 
    FROM @tb
    WHERE attempts != 0

Upvotes: 1

Related Questions