Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    28

    Unanswered: sql update command from temporary table

    I have 3 tables t1, t2, and t3.

    Each has a primary key of 'job_no'.

    t3 has a field called 'status'.

    I wish to update t3.status by setting it to “XXX” when certain conditions are met.

    My syntax looks like this :-

    select t1.job_no
    from t1, t2, t3
    where t1.job_no = t2.job_no
    and t2.job_no = t3.job_no
    and (more conditions)
    into temp ttemp;
    update t3
    set t3.status = “XXX”
    where ttemp.job_no = t3.job_no

    but I get error :- 522: Table (ttemp) not selected in query

    Can anyone advise how to get around this ?

    Thanks

  2. #2
    Join Date
    Jan 2011
    Posts
    14

  3. #3
    Join Date
    Jul 2009
    Posts
    28

    sql update command from temporary table

    Thanks John
    Unfortunately, my requirement has changed since my first post.
    Rather than setting t3 status to 'XXX', I now need to set it to the
    value of status now being held in the t1 table.
    I don't think the examples in "Update using an Inner Join"
    will do for my new scenario.
    Any further advise would be most grateful !

  4. #4
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Using your original methodology you could:

    select t1.job_no, t1.status
    from t1, t2, t3
    where t1.job_no = t2.job_no
    and t2.job_no = t3.job_no
    and (more conditions)
    into temp ttemp;

    update t3
    set t3.status = (select ttemp.status
    from ttemp
    where ttemp.job_no = t3.job_no)
    where t3.jobno in (select ttemp.job_no from ttemp);

  5. #5
    Join Date
    Jul 2009
    Posts
    28

    sql update command from temporary table

    Hi mjl
    Many thanks : your code works great.

    Can I ask another question ?
    If table t3 has more than one field to be updated from t1,
    then I can enhance the 1st part as follows :
    Code:
      select  t1.job_no, t1.status, t1.f2, t1.f3 
      from    t1,  t2,  t3
      where  t1.job_no = t2.job_no
      and     t2.job_no = t3.job_no
      and (more conditions)
      into temp ttemp;
    But how would the update command look ?

    Code:
      update t3
      set t3.status =  (select ttemp.status    from ttemp
                              where ttemp.job_no = t3.job_no)
      set t3.f2       =  (select ttemp.f2         from ttemp
                              where ttemp.job_no = t3.job_no)
      set t3.f3       =  (select ttemp.f3         from ttemp
                              where ttemp.job_no = t3.job_no)
      where t3.jobno in (select ttemp.job_no from ttemp);
    doesn't seem to work.

    many thanks again.

  6. #6
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    You can have only one "set" in an update statement & the various values are separated with commas. Using your method this MAY work
    (untested but inefficient) as long as the "where" condition of the subquery(s) returns only 1 row:

    update t3
    set t3.status = (select ttemp.status from ttemp
    where ttemp.job_no = t3.job_no),
    t3.f2 = (select ttemp.f2 from ttemp
    where ttemp.job_no = t3.job_no),
    t3.f3 = (select ttemp.f3 from ttemp
    where ttemp.job_no = t3.job_no)
    where t3.jobno in (select ttemp.job_no from ttemp);

    ================================================

    this will work, same "subquery only returns 1 row" rule applies

    update t3
    set (t3.status, t3.f2, t3.f3) = (select ttemp.status, ttemp.f2, ttemp.f3
    from ttemp
    where ttemp.jobno = t3.jobno)
    where t3.jobno in (select ttemp.job_no from ttemp);
    Last edited by mjldba; 02-17-11 at 10:48.

Posting Permissions

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