Update a column based on value on another column in SQL table

I have a SQL Table that I want to update a column called "UpdateColumn" based on [Column 1], Rownumber = 1 and Usage = R and Elements = '12'

This is my query so far but I cannot get the right results:
UPDATE Table1
 SET    UpdateColumn = [Column 1] 
 WHERE  Rownumber = '1'
 AND    Usage = 'R'
AND Elements = '12'
 AND FILENUMBER  = FILENUMBER

Open in new window


See attached.

Please help.

I really appreciate all your help.

Thank you.
SQL_Table_and_Output.xlsx
Queennie LAsked:
Who is Participating?
 
Ryan ChongCommented:
@Queennie L

I did not realize some of [Column 1] has data with semicolon and numbers.
for my example, quick fix would be changing:

if @last_column1 = '' or (len(@column1)> 2 and @cnt > 1)

to:

if @last_column1 = '' or (len(@column1)> 3 and @cnt > 1)
0
 
Ryan ChongCommented:
just wondering if there's a common column available to "group" your records together?
0
 
Queennie LAuthor Commented:
@Ryan:

I don't think there is.

I tried it with filename but it did not give me the right results.

Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
not like this but probably got to do it using Cursor, like:

declare @uniqueid int;
declare @column1 nvarchar(100);
declare @last_column1 nvarchar(100);
declare @cnt int;

declare cur cursor for  
select uniqueid, [column 1]
from Table1
order by uniqueid, RowNumber

open cur  
fetch next from cur into @uniqueid, @column1

set @last_column1 = ''
set @cnt = 0

while @@fetch_status = 0  
begin  
       if @last_column1 = '' or (len(@column1)> 2 and @cnt > 1)
			set @last_column1 = @column1
			
	   update a set updatecolumn = @last_column1 
	   from Table1 a
	   where uniqueid = @uniqueid
	   set @cnt = @cnt + 1
	   
       fetch next from cur into @uniqueid, @column1  
end 

close cur;
deallocate cur;

select * from Table1
order by uniqueid

Open in new window

0
 
Queennie LAuthor Commented:
@Ryan Chong:

Thank you again for your help.

I will run and test and will get back to you.

Thank you.
0
 
NerdsOfTechTechnology ScientistCommented:
Ryan, hope this helps; I made a dynamic range of the records, but you would probably know how to tie these into an update.

I figure the logic would be if the Usage != 'R' rows is BETWEEN L and H, then SET UpdateColumn with the range's Column1:

  SELECT y1.`UniqueID` AS `L`, y2.`UniqueID`-1 AS `H`, y1.`Column1` FROM
  (
   SELECT `UniqueID`, `Column1`
   FROM Table1
   WHERE `USAGE` = 'R'
  ) y1
  LEFT JOIN
  (
   SELECT `UniqueID`, `Column1`
   FROM Table1
   WHERE `USAGE` = 'R'
    UNION ALL
   SELECT * FROM
   (
    SELECT `UniqueID`, `Column1`
    FROM Table1
    ORDER BY `UniqueID` DESC
    LIMIT 0,1
   ) t2
  ) y2
  ON 
   y2.`UniqueID` = (SELECT MIN(`UniqueID`) FROM Table1 WHERE `USAGE` = 'R' AND y1.`UniqueID` < `UniqueID`)

Open in new window


L      H      Column1
16      28      AAA00208667301
29      39      AAA00208672801
40      50      AAA00208640201
51      82      AAA00207582801
83      (null)      AAA00208384701

http://sqlfiddle.com/#!9/714cf9/85
0
 
Queennie LAuthor Commented:
@Ryan Chong:

I like the cursor syntax. It is perfect. It works but when I run and check it. I did not realize some of [Column 1] has data with semicolon and numbers. I tried to change the data types but it did not work.

Please see attached.

Thank you again for your help.
SQL_Table_and_Output_Test.xlsx
0
 
Ryan ChongCommented:
@NerdsOfTech,

not able to test for your suggestion as the database specified is MS SQL not MySQL.
1
 
Queennie LAuthor Commented:
@Ryan Chong:


It works.

Thank you again for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.