• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

SQL Update

i am using a slowly changing dimension to update my table. but i need to incluse a few checks.
so after the table has been updated i am using another sp to do the checks.

what i need to do is, if the value in th ecode is NULL then use the previous code.

rowid   datecreated    validfrom   validto   iscurrent   businesskey   code      recdel
1          18/09               18/09           17/09    0                 C3                   CW        NO
2          18/09               18/09           NULL     1                 C3                   NULL    NO

Thanks.
0
aneilg
Asked:
aneilg
  • 7
  • 3
  • 2
  • +2
7 Solutions
 
Saurabh BhadauriaCommented:
what I understood you can implement in below manner..


update your_table set
your_column = case when @update_value is null then your_column else @update_value                                end
0
 
aneilgAuthor Commented:
sorry, but i dont understand how this wil work.

update [WARD_SCD2_HISTORY]
set
 = 
case when @update_value is null 
then [Code] 
else @update_value                               
end 

Open in new window

the code chould be any value, so i cannot use the same value
0
 
Saurabh BhadauriaCommented:
if the new value is null, in this case query will update the same existing column value
and if is not null then the column will updated with new value (@update_value)

Hope this will help ..
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
aneilgAuthor Commented:
all its done is set all the values in the code column to null.

i want to use previous value if null
0
 
aneilgAuthor Commented:
i've been looking at CTE and coalesce
0
 
Pratima PharandeCommented:
try something like this with self join on same table

Update t1

Set t1.code = t2.code

From tablename t1 inner join
tablename t2 on t1.rowid = t2.rowid - 1
where t1.code is null
0
 
aneilgAuthor Commented:
still not what i am looking for.
0
 
Saurabh BhadauriaCommented:
What do you mean by " previous value " here
0
 
Jared_SCommented:
This uses the last validto field with a non-null code value.


Update t1
Set t1.code = (select code from tablename where validto = (select max(validto) where code is not null)
from tablename t1 
where t1.code is null

Open in new window

0
 
aneilgAuthor Commented:
sorry to be a pain.
Incorrect syntax near the keyword 'from'.
0
 
aneilgAuthor Commented:
this works for reference.


UPDATE  TABLE  
SET     Code = c2.Code  
OUTPUT  Inserted.*  

FROM    TABLEc  
        INNER JOIN (  
          SELECT  c.[DateCreated]
                  , c.[BusinessKey]
                  , Code = COALESCE(c.Code, cprevious.Code)
          FROM    TABLEc
                  INNER JOIN (
                    SELECT  c.[DateCreated]
                            , c.[BusinessKey]
                            , MaxDate = MAX(cdates.[DateCreated])
                    FROM    TABLEc
                            LEFT OUTER JOIN (
                              SELECT  [DateCreated]
                                      , [BusinessKey]
                              FROM    TABLE
                            ) cdates ON cdates.[DateCreated] < c.[DateCreated] AND cdates.[BusinessKey] = c.[BusinessKey]
                    GROUP BY
                            c.[DateCreated], c.[BusinessKey]
                  ) cmax ON cmax.[DateCreated] = c.[DateCreated] AND cmax.[BusinessKey] = c.[BusinessKey]                  
                  LEFT OUTER JOIN TABLE cprevious ON cprevious.[DateCreated] = cmax.MaxDate AND cprevious.[BusinessKey] = cmax.[BusinessKey]
        ) c2 ON c2.[DateCreated] = c.[DateCreated]  
                AND c2.[BusinessKey] = c.[BusinessKey]
0
 
LIONKINGCommented:
sorry to be a pain.
Incorrect syntax near the keyword 'from'.

This is because of a missing parenthesis.

Update t1
Set t1.code = (select code from tablename where validto = (select max(validto) where code is not null))
from tablename t1 
where t1.code is null

Open in new window

0
 
Jared_SCommented:
Thanks LIONKING - I left the table name out of the sub query.


Update t1
Set t1.code = (select code from tablename where validto = (select max(validto) where code is not null)
from tablename t1 
where t1.code is null

Open in new window

0
 
aneilgAuthor Commented:
partly answered.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now