# Simplify nested Update, Select and IsNull statement

**URL:** <https://forum.inductiveautomation.com/t/simplify-nested-update-select-and-isnull-statement/2117>\
**Category:** Archived Topics\
**Created:** [August 11, 2010, 12:39pm UTC](https://forum.inductiveautomation.com/t/simplify-nested-update-select-and-isnull-statement/2117 "2010-08-11T12:39:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jared](https://avatars.discourse-cdn.com/v4/letter/j/848f3c/32.png) [@Jared](https://forum.inductiveautomation.com/u/Jared)\
**Post date:** [August 11, 2010, 12:39pm UTC](https://forum.inductiveautomation.com/t/simplify-nested-update-select-and-isnull-statement/2117/1 "2010-08-11T12:39:22Z")

</div>

This question started as an email exchange.

[quote]Good morning,

I am trying to streamline a command and can’t seem to get the syntax right (if it is even possible). The below command is my current situation. My goal is to have the select ISNULL replace the {B4} in the update statement. I have to do this command for 96 separate positions ({POS}) and would like to do all 96 at once, but can’t the way it is now. I would replace the {POS} with 1 through 96 if I can get the ISNULL statement to update a specific record in my db1.table12\_check table. Both statements function properly as shown.  
MySql 5.1  
Factory SQL 4.2.7

_ **What I have** _\*  
select ISNULL((  
SELECT(  
SELECT Material From db1.table12 Where Station = ({POS})  
) FROM fw\_db.mat\_list WHERE Style =  
(Select Style FROM db1.table12 WHERE Station = ({POS}))  
));

update db1.table12\_check  
set db1.table12\_check.mat\_chk = {B4}  
where db1.table12\_check.station = {POS};

_ **What I want** _  
update db1.table12\_check  
set db1.table12\_check.mat\_chk =  
(  
select ISNULL((  
SELECT(  
SELECT Material From db1.table12 Where Station = ({POS})  
) FROM fw\_db.mat\_list WHERE Style =  
(Select Style FROM db1.table12 WHERE Station = ({POS}))  
))  
)  
where db1.table12\_check.station = {POS};

Thanks for you help  
Jared[/quote]

Come to find out, the Update with the included IsNull statement was working, FSQL was just giving a bogus write-back value, it was sending the table the correct info (1 or 0)

Here is Colby’s response

[quote] Hi Jared,

At first glance, these queries seem very confusing to me. It appears that you’re trying to determine whether 2 conditions are met: a) there’s a matching join entry between db1.table12 and fw\_db.mat\_list on Style, and b) material is not null on db1.table12. If that’s correct, I think you can rewrite your query to remove the nested selects:

SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station={POS} and b.style=a.style

This joins your two tables directly, across the different databases, as is allowed in MySQL 5.1. The count(\*)=0 will be true if there isn’t a matching “style” value in both tables, and IsNull(a.material) will check the material value if the join does return a value.

Now, all of that is just making your select statement easier to read. The problem is the update, right?  
I don’t see why you couldn’t do what you originally had in mind…  
UPDATE db1.table12\_check SET db1.table12\_check.mat\_chk=(SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station={POS} and b.style=a.style) WHERE db1.table12\_check.station={POS}

However, you may be able to get even fancier, and update all rows at once with the following:

UPDATE db1.table12\_check c SET db1.table12\_check.mat\_chk=(SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station=c.station and b.style=a.style)

You see, what I did there was alias the update table to “c”. Then, in the select, instead of referencing a specific POS, I pull the station from the table we’re updating. So, for each row in “table12\_check”, the sub select will be run with the correct station.

Hopefully this is clear enough- this is definitely a tricky problem. The forum would definitely be a good place to post something like this, _snip_

Please let me know if you have any questions about this, or if I got something wrong in my understanding of what you want to accomplish.

Regards,

Colby Clegg  
Inductive Automation[/quote]

---

<div class="post-metadata">

**Author:** ![Jared](https://avatars.discourse-cdn.com/v4/letter/j/848f3c/32.png) [@Jared](https://forum.inductiveautomation.com/u/Jared)\
**Post date:** [August 11, 2010, 12:48pm UTC](https://forum.inductiveautomation.com/t/simplify-nested-update-select-and-isnull-statement/2117/2 "2010-08-11T12:48:24Z")

</div>

So to continue the question.

Thanks for the reply Colby, you were accurate enough in your guess at what I was trying to accomplish. I should first clarify that my naming scheme of table12 has nothing to do with a database table, that is a production table at our factory, just to clarify before any confusion occurs.  
[color=#000080]  
-What I am doing-  
3 tables involved...  
[ul]db1.table12 = parts currently being produced, 96 total stations  
db1.table12\_check = results from material approval checks  
fw\_db.mat\_list = approved material listing[/ul]

I am taking the "style" for each of the 96 stations and running a check to see if the style & material combination in fw\_db.mat\_list returns a value or is Null. I store that result in db1.table12\_check for use later. Like I said earlier, your assumption was close enough that your code is right for my application.[/color]

This statement worked perfect..

> [@](#):
>
> SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station={POS} and b.style=a.style

Inserting that into the update statement almost worked. I had to remove the alias "c" from db1.table12\_check to make it work. MySql was giving me an error of

"unknown column 'db1.table12\_check.mat\_list' in 'field list.'"

Your suggestion..

> [@](#):
>
> UPDATE db1.table12\_check c SET db1.table12\_check.mat\_chk=(SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station=c.station and b.style=a.style)

working command..

[quote]  
UPDATE db1.table12\_check SET db1.table12\_check.mat\_chk=(SELECT count(\*)=0 or IsNull(a.material) FROM db1.table12 a, fw\_db.mat\_list b WHERE a.station=db1.table12\_check.station and b.style=a.style)[/quote]
