Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Update with Subquery

    I know this seems to be a common question and I thought i understood the basics to using subqueries to do updates, but I keep working on this and I can't get this to work. I can't seem to limit my where clause to only the records I want updated.

    The idea is that I have a table scormPackageProperties with specific records that I want to copy to other records in the same table, but I keep getting "Can't update to null value" because apparently I'm updating all of the records in my Update statement.

    I should point out that la.id is the PK.

    Thanks in advance.

    Code:
     
    update (select  prop.*, la.id, la.code, la.title, la.sticiCategory from drLearningActivity la
                  inner join ScormPackage pkg on pkg.learning_activity_id=la.id
                  inner join ScormPackageProperties prop on prop.scorm_package_id=pkg.scorm_package_id
                  where lower(la.sticiCategory) = 'template_one') T1 
                         set (show_finish_button) =
               --now begin subquery
                             (SELECT t2.show_finish_button
                                  from ScormPackageProperties T2 
                                        INNER JOIN  scormPackage PKG ON t2.scorm_package_id=pkg.scorm_package_id
                                        inner join drLearningActivity la on la.id=pkg.learning_activity_id
                                  where  lower(la.code)=lower(t1.sticiCategory) and t1.id=la.id)
             where exists 
                   (select la.id from drLearningActivity la 
                           where la.id=t1.id
                          and lower(la.sticiCategory) in ('template_one'))

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which single table do you want to UPDATE?
    Code:
    UPDATE (SELECT prop.*, 
                   la.id, 
                   la.code, 
                   la.title, 
                   la.sticicategory 
            FROM   drlearningactivity la 
                   inner join scormpackage pkg 
                           ON pkg.learning_activity_id = la.id 
                   inner join scormpackageproperties prop 
                           ON prop.scorm_package_id = pkg.scorm_package_id 
            WHERE  Lower(la.sticicategory) = 'template_one') T1 
    SET    ( show_finish_button ) = 
           --now begin subquery 
           (SELECT t2.show_finish_button 
            FROM   scormpackageproperties T2 
                   inner join scormpackage PKG 
                           ON t2.scorm_package_id = pkg.scorm_package_id 
                   inner join drlearningactivity la 
                           ON la.id = pkg.learning_activity_id 
            WHERE  Lower(la.code) = Lower(t1.sticicategory) 
                   AND t1.id = la.id) 
    WHERE  EXISTS (SELECT la.id 
                   FROM   drlearningactivity la 
                   WHERE  la.id = t1.id 
                          AND Lower(la.sticicategory) IN ( 'template_one' ))
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    184
    Sorry, that wasn't clear was it. The table I'm updating is scormPackageProperties.

    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And what columns in scormPackageProperties by what data(column values) of what table(s) do you want to update?

  5. #5
    Join Date
    May 2004
    Posts
    184
    Thanks,

    scormpackageproperties.show_finish_button is both the field that is being updated and the source of the value that is being copied.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Remove subquery including joins after UPDATE,
    and add necessary joins in a subqury in WHERE clause.
    At least, it might be necessary to join ScormPackage pkg and ScormPackageProperties T2 in WHERE clause.
    Code:
    UPDATE scormPackageProperties t1
       SET show_finish_button
        = (SELECT ...
    ...
          )
     WHERE EXISTS
          (SELECT 0
    ...
          )

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Generally speaking,
    a subquery in WHERE clause should be same as a subquery in SET clause or more restrictive except SELECT list, to avoid update to null value.

  8. #8
    Join Date
    May 2004
    Posts
    184
    Tonkuma,

    Thanks. I originally added the subquery to the UPDATE to eliminate multiple rows being returned. I'm assuming that my WHERE clause can't do that or am I wrong.

    Thank you.

  9. #9
    Join Date
    May 2004
    Posts
    184
    Sorry, I should have said WHERE EXISTS.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You were not checking existence of ScormPackageProperties T2 in your UPDATE subquery nor WHERE EXISTS subquery.
    So, it would be possible to SET to null value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •