update query: How to set zero if subquery returns no result

[javakid]

Hi MySQL Gurus,

I have a query as in following format:
[code=mysql]
update tbl1, ( subquery )as tbl2
set tbl1.col1 = IFNULL(tbl2.col 1,0)
where tbl1.col2 = tbl2.col2[/code]Here, Actually i am returning a count of a column from tbl2 which is set to col2 of tbl1. it is perfectly running whenever tbl2 has count of 1 or more. when there is no count or theres is no count it does not return zero so ultimately my query fails for this particular condition.

How can i assign a value zero to tbl1.col2 when there is no result exist for that particular row

Thanks in advance

[ronverdonk]

Instead of wasting time making code bold, adhere to the Posting Guidelines and
enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[javakid]

Instead of wasting time making code bold, adhere to the Posting Guidelines and
enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

I am really sorry for that. I will hence forth use the code tag wherever necessary.

please Can anybody answer my question ?

Thanks in advance

[chaarmann]

Your where-statement filters out the records with no match, so you should not give it at all.
That means, you must do for every row of the original table, not for every record of a cartesian product.

update  tbl1
 set      tbl1.col1 = IFNULL([U]select tbl2.col1 from tbl1, (subquery) as tbl2 where tbl1.col2 = tbl2.col2[/U] ,0);