SQL Update query on query result

Question

I have 2 tables like:

Table 1

SID     Sdefinition   CValue
4057    s1             32
4058    s2             
4059    s3             6
4060    s4

Mapping_tbl

SID     SINID   ECFID   SID-SINID
4057    1099    4027e   1099_4057
4058    1099    4027e   1099_4058
4059    1121    4003e   1121_4059
4060    1121    4003e   1121_4060

Query1

  SELECT Mapping_tbl.SID, Table1.Sdefinition, Table1.CValue
  FROM Table1 INNER JOIN Mapping_tbl ON Table1.SID= Mapping_tbl.SID;

Query1(Result)

   SID  Sdefinition CValue
   4057    s1      32
   4058    s2      
   4059    s3      6
   4060    s4        

I have a situation that I wanted to update query table (Query1) i.e set field(Cvalue) to 0 if it contains null. I am using update query like

   Update Query1 Set CValue = 0 Where CValue Is Null;

The query table (query1) gets updated and sets Cvalue to 0 if it contains nulls, and it also updates(set 0) Table1 where Cvalues are null.

How can I avoid updating Table1? Any suggestions.

Answer 1

It seems you don't want to change the values stored in your table, and you want the query to display zero when CValue is Null. So you can have the query substitute zero for Null in its result set only (without changing values in the table) ...

SELECT m.SID, t.Sdefinition, IIf(t.CValue Is Null, 0, t.CValue) AS [CValue]
FROM Table1 AS t INNER JOIN Mapping_tbl AS m
ON t.SID= m.SID;

For a query in an Access session, you could use the VBA Nz() function ...

SELECT m.SID, t.Sdefinition, Nz(t.CValue Is Null, 0) AS [CValue]
FROM Table1 AS t INNER JOIN Mapping_tbl AS m
ON t.SID= m.SID;

I used aliases for the table names. But you don't need them for Null substitution to work. I just prefer aliases.