Update Null values
-
Hi There,
I need to update null values in a column like this,
Prov. Brabant Wallon
NULL
Prov. Hainaut
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Prov. Liège
NULL
NULL
NULL
NULL
....update to this
Prov. Brabant Wallon
Prov. Brabant Wallon
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Hainaut
Prov. Liège
Prov. Liège
Prov. Liège
Prov. Liège
Prov. Liège
What is the easy way to do this?
All help is appreciated
-
What defines the order of the values in your column?
John
-
I have a uniqe code like this...
BE31 Prov. Brabant Wallon
BE310 NULL
BE32 Prov. Hainaut
BE321 NULL
BE322 NULL
BE323 NULL
BE324 NULL
BE325 NULL
BE326 NULL
BE327 NULL
BE33 Prov. Liège
BE331 NULL
BE332 NULL
BE333 NULL
BE334 NULL
Regards Joe joe
-
As your primary key has some meaning you can get away with something like:
-- *** Test Data ***
DECLARE @t TABLE
(
Code varchar(5) NOT NULL PRIMARY KEY
,Prov varchar(25) NULL
)
INSERT INTO @t
SELECT 'BE31', 'Prov. Brabant Wallon' UNION ALL
SELECT 'BE310', NULL UNION ALL
SELECT 'BE32', 'Prov. Hainaut' UNION ALL
SELECT 'BE321', NULL UNION ALL
SELECT 'BE322', NULL UNION ALL
SELECT 'BE323', NULL UNION ALL
SELECT 'BE324', NULL UNION ALL
SELECT 'BE325', NULL UNION ALL
SELECT 'BE326', NULL UNION ALL
SELECT 'BE327', NULL UNION ALL
SELECT 'BE33', 'Prov. Liège' UNION ALL
SELECT 'BE331', NULL UNION ALL
SELECT 'BE332', NULL UNION ALL
SELECT 'BE333', NULL UNION ALL
SELECT 'BE334', NULL
-- *** End Test Data ***
-- Do the update
UPDATE T1
SET Prov = T2.Prov
FROM @t T1
JOIN @t T2
ON LEFT(T1.Code, 4) = T2.Code
AND T1.Code <> T2.Code
-- Check Result
SELECT *
FROM @t
-
Thanks alot Ken !
I understand the idea with joining through a truncate of the code, but how do I put my exsiting table with data in a @t Table in an easy way?
Joe joe
-
Joe
Just substitute the name of your table for @t in the query. Obviously you will also need to change the column names to match yours.
John
-
Someting like this?
UPDATE A.Mytable
SET Prov = A.Mytable.Prov
FROM A.MyTable
JOIN B.MyTable
ON LEFT(A.MyTable.Code, 4) = B.MyTable.Code
AND A.MyTable.Code <> B.MyTable.Code
Joe
-
Hi there, The following scripts....
DECLARE @t TABLE
(
Code varchar(5) NOT NULL PRIMARY KEY
,Country nvarchar(255) NULL
,[Level 1] nvarchar(255) NULL
,[Level 2] nvarchar(255) NULL
,[Level 3] nvarchar(255) NULL
)
INSERT INTO @t (Code, Country, [Level 1], [Level 2], [Level 3])
SELECT Code, Country, [Level 1], [Level 2], [Level 3]
FROM [Test_II].[dbo].[nuts_Names]
Update T1
Set Code = T2.Code
From @t T1
Join @t T2
On LEFT(T1.Code, 4)= T2.Code
And T1.Code <> T2.Code
Select *
From @t
Gives me the following message......
Msg 515, Level 16, State 2, Line 9
Cannot insert the value NULL into column 'Code', table '@t'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
As I understand there is a conflict between using Insert Into with NOT NULL columns - but what can/should I do?
Once again thanks for your time and advice....
Joe
-
Everthing works!
I just had to cahnge the
DECLARE @t TABLE
(
Code varchar(5) NOT NULL PRIMARY KEY
To
DECLARE @t TABLE
(
Code varchar(5) NULL
Joe
-
Joe
So the Code column in your nuts_Names table has NULLs in it? How do you want to deal with those? It would help if you could provide the CREATE TABLE statement for that table as well as some sample rows from it.
By the way, you don't need to use a table variable to do this update. Ken only used that in his example because he didn't know the name of your actual table. But let's sort out the NULL issue before we worry about that.
John
-
Opps, I have discovered that five values was Null due to a coversion error because my source for the table was excel, and excel coverted the values Dec01, Dec02, Dec03, Dec04, Dec05 to Date, which again was converted to NULL when I imported from the Excel.
The error is corrected, and there is no NULL values in the Code column.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply