August 6, 2019 at 10:49 am
Hi Team,
please help on this below update query.
My data looks like:
create table #a(a int,b int,c int,d int)
INSERT INTo #a
select 0,1,0,2
union ALL
select 0,2,0,3
union ALL
select 0,3,0,4
select * from #a
a b c d
0 1 0 2
0 2 0 3
0 3 0 4
expected data: update if the whole row value = 0 then update to null (if should happen dynamically)
we dont know which column value become 0.
a b c d
null 1 null2
null 2 null 3
null 3 null 4
August 6, 2019 at 11:49 am
are you looking for this?
update #a
set a = case
when a = 0 then NULL
else a
end,
b = case
when b = 0 then NULL
else b
end,
c = case
when c = 0 then NULL
else c
end,
d = case
when d = 0 then NULL
else d
end
I want to be the very best
Like no one ever was
August 6, 2019 at 1:57 pm
Probably cleaner to check for 0 values as you INSERT the row:
INSERT INTo #a
select NULLIF(0,0),NULLIF(1,0),NULLIF(0,0),NULLIF(2,0)
union ALL
select NULLIF(0,0),NULLIF(2,0),NULLIF(0,0),NULLIF(3,0)
union ALL
select NULLIF(0,0),NULLIF(3,0),NULLIF(0,0),NULLIF(4,0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2019 at 3:30 pm
are you looking for this?
update #a
set a = case
when a = 0 then NULL
else a
end,
b = case
when b = 0 then NULL
else b
end,
c = case
when c = 0 then NULL
else c
end,
d = case
when d = 0 then NULL
else d
end
This will update every field of every record in the table, very time that you run it. You may want to add a filter to the update statement. It will still update all the fields, but only in affected records.
WHERE a = 0 OR B = 0 OR c = 0 OR d = 0
August 6, 2019 at 4:46 pm
Unfortunately what you posted is not a table. A table must have a key and those column(s) he cannot be NULL-able by definition.
CREATE TABLE Foobar
(a INTEGER,
b INTEGER NOT NULL PRIMARY KEY, --- why not??
c INTEGER,
d INTEGER,
I hope you know that an update is not a query, but a statement. The difference is a fundamental programming concept that you should know. It's probably better to filter this on the way in then to insert it then switch it around them with another pass through the table. You might also want to stop using the old Sybase union insertion.
INSERT INTO Foobar
VALUES
( NULLIF(0, 0), 1, NULLIF(0, 0), NULLIF(2, 0)),
NULLIF(0, 0), 2, NULLIF(0, 0), NULLIF(3, 0)),
(NULLIF(0, 0), 3, NULLIF(0, 0), NULLIF(4, 0));
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply