• Replies 8 replies
  • Subscribers 337 subscribers
  • Views 85 views
  • Users 0 members are here
Related

Using SQL to inactivate workers: what am I doing wrong?

Elizabeth Marvin
$organization

Hi all! I am trying to do a cleanup of all of our workers by making around ~175 of them inactive, and as a beginner SQL user I'd appreciate the community's review/advice on my code.

Here is what I'm trying to update on these constituent records:

And I have a static list of all of the workers that need to be made inactive (list # 133113).

So the SQL script I came up with is as follows:

Use impresario
select *
from T_LIST_CONTENTS a
left outer join TX_CUST_WORKER_TYPE b
on a.customer_no=b.worker_customer_no
where a.list_no='133113'
and b.inactive = 'n'

--below is a separate part of the code to run.
begin transaction
update TX_CUST_WORKER_TYPE
set inactive = 'y'
where worker_customer_no in
(select b.worker_customer_no from T_LIST_CONTENTS a
left outer join TX_CUST_WORKER_TYPE b on a.customer_no=b.worker_customer_no
where a.list_no='133113')
commit

I tried this in Test, but I must have done something wrong; afterwards, opening any of the affected accounts would cause Test to crash. (I did a Prod to Test copy last night, so Test is now working properly again).

Many thanks in advance for any tips or advice!

Liz 

Parents
  • $organization

    ,

    Hello from slightly to the north of you!  is correct, I believe.  I just ran your script against an isolated worker in our own TEST database with the capital 'Y' (always recommended, though sometimes it should not matter, on single character things like this, I always make sure to mimic what is already being done), and everything worked just fine for me.

    If not that, there might be something else in play here.

    John A. Moskal II

  • $organization in reply to John A. Moskal II

    If not that, it might be this.  You are selecting b.worker_customer_no from the two tables that you have joined on a LEFT OUTER JOIN (or LEFT JOIN).  That SELECT will always return all the results from table "a".  It will however also return NULLS in place of the columns from table "b" if there is no match.  So, if there happens to exist customer_nos in table "b" that do NOT exist in table "a" (which I would expect is VERY highly likely), those updates would be trying to set things to NULLS instead of a 'Y' or 'N'.  That could very well make Tessitura react poorly as the "inactive" column is only supposed to have one of those two options.

    Try changing your LEFT OUTER JOIN to just a straight JOIN.  That will ONLY link those two tables on the values that match and eliminate NULL results from the right hand columns returned.

    Also, if you ever want to chat SQL stuff in person, since we happen to be in the same area, please feel free to reach out to me.  I actually will be headed down to see the ISO in a few weeks myself (I am even a subscriber!).

    Hope that helps.

    John A. Moskal II

Reply
  • $organization in reply to John A. Moskal II

    If not that, it might be this.  You are selecting b.worker_customer_no from the two tables that you have joined on a LEFT OUTER JOIN (or LEFT JOIN).  That SELECT will always return all the results from table "a".  It will however also return NULLS in place of the columns from table "b" if there is no match.  So, if there happens to exist customer_nos in table "b" that do NOT exist in table "a" (which I would expect is VERY highly likely), those updates would be trying to set things to NULLS instead of a 'Y' or 'N'.  That could very well make Tessitura react poorly as the "inactive" column is only supposed to have one of those two options.

    Try changing your LEFT OUTER JOIN to just a straight JOIN.  That will ONLY link those two tables on the values that match and eliminate NULL results from the right hand columns returned.

    Also, if you ever want to chat SQL stuff in person, since we happen to be in the same area, please feel free to reach out to me.  I actually will be headed down to see the ISO in a few weeks myself (I am even a subscriber!).

    Hope that helps.

    John A. Moskal II

Children
No Data