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:
preferred updated table:
Add a comment
|
4 Answers
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
Comments
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...
Comments
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
1 Comment
GuidoG
you could do
where isnull(attempts, '') = ''to fix the ambigious problem you mentionedDeclare 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
