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.

Table one is the underlying table, it's going to update. Are you just trying to display 0 instead of null in the query output? — Po-ta-toe, Jun 18 '15 at 19:51
possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Maciej Los, Jun 18 '15 at 19:55
@Carl Sixsmith Yes, just trying to display 0 instead of null — sam, Jun 18 '15 at 20:02

HansUp · Accepted Answer · 2015-06-18T20:02:32.607

2

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.

edited Jun 18 '15 at 20:02

answered Jun 18 '15 at 19:54

HansUp

95,961
11
77
135

Didnt realise it was access, this is a better answer than mine – Po-ta-toe Jun 18 '15 at 19:55
1

Yes, Access `IsNull` is different than SQL Server's. In Access, it accepts only one argument and returns a boolean ... but it doesn't substitute anything for the Null value. – HansUp Jun 18 '15 at 19:57
Worked like charm, you are brilliant HansUp – sam Jun 18 '15 at 20:18

SQL Update query on query result

1 Answers1