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]