Simplify nested Update, Select and IsNull statement

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]

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…

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…

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]