Update only in case where its not null

Question

I want update a column(a) from table1 with select from table2 but making update only when in table2 existing column(b) is not null. I dont want use where statement (is not null) cause it will influence all my code.

Example of my code:

update table1 set column(a) = (select column(b) from table2)

I'm trying something like this

update table1 set column(a) = not null(select column(b) from table2)

Example:

update ExpressMarketCheck set Barcode = (select barcode from ExpressMarket), Name=(select name from expressmarket), price=(select price from expressmarket)

What does "influence all my code" mean? Why can you nod use a simple, standard `WHERE` clause? — D Stanley, Sep 27 '16 at 12:48

score 1 · Accepted Answer · answered Sep 27 '16 at 12:46

1

You can use this query to achieve your constraint.

Update ExpressMarketCheck set Barcode = (select barcode from ExpressMarket where barcode IS NOT NULL)

answered Sep 27 '16 at 12:46

vignesh

83
8

1

Uncorrelated sub-query? – jarlh Sep 27 '16 at 12:47
thats exactly what he doesn't want. " I dont want use where statement (is not null)" – Nebi Sep 27 '16 at 12:47
Thanks for the info guys. Initially that constraint wasn't there when I post the answer. – vignesh Sep 27 '16 at 12:50
Its ok i will it works with little changes in my code! Thank you! – DiH Sep 27 '16 at 12:50

Muhammad Usman · Answer 2 · 2016-09-27T12:50:37.093

1

mySQL has an IFNULL function, so you could do:

UPDATE your_table_name
SET your_column_name= "data",
scan_created_date = ISNULL( your_column_name, "data" )
WHERE id = X

edited Sep 27 '16 at 12:50

answered Sep 27 '16 at 12:48

Muhammad Usman

9,766
21
39

in SQL-Server you can use `ISNULL()` – Nebi Sep 27 '16 at 12:49
thanks for pointing to it -:) – Muhammad Usman Sep 27 '16 at 12:50

score 1 · Answer 3 · answered Sep 27 '16 at 12:51

I think you want a join:

update emc
    set Barcode = em.barcode, 
        Name = em.name,
        price= em.price
     from ExpressMarketCheck emc join
          expressmarket em
          on emc.?? = em.??;

I cannot tell from your question what columns should be used for the join. The ?? are placeholders.

Update only in case where its not null

3 Answers3