-4

I hope my questions makes sense but what i am trying to do (which in my mind is obscenely simple which makes it more frustrating that i cant figure it out) is to change a column that consists of NULLS to a piece of data.

For example:

 USE [DB_A]
 insert into dbo.address (City, State)
 values ('test', 'ing')
 where City IS NULL

I hope that makes sense. The 'City' column DOES allow NOT NULL aka there is allowed to be data in there.

Later on i will want to reverse this process with something like:

 USE [DB_A]
 insert into dbo.address (City, State)
 values (NULL, NULL)
 where City = 'test'

Does anyone please have any ideas?

EDIT: Sorry for any confusion all, i'm updating this with what i hope is a better explanation of my problem.

All i'm trying to do is change 4000+ rows in 2 columns (city, state) to no longer be NULL by inserting data into them. 'Update' doesnt work because it returns "0 rows updated" and 'Insert' errors with the "where" clause.

6
  • 4
    Your second query does not seem to make sense. You can't INSERT data and also provide a WHERE clause. Also, could you provide the table definition of the dbo.Address table?
    – mendosi
    Nov 7, 2016 at 5:35
  • 1
    Do you possibly want to update the value of existing rows? Otherwise, I'm hard-pressed to guess what you'd want either of your insert statements to do. Nov 7, 2016 at 5:41
  • My apologies for the confusion. I simply want to update rows where the 'City' is NULL to have a value instead. I later want to reverse this process and change it back to a NULL or change it to a blank value like ' '.
    – L.Moy
    Nov 7, 2016 at 6:03
  • 1
    There is a very basic misunderstanding here. All columns allow not null values, unless there is some very weird constraint. When a column has NOT NULL, it means that NULLs are not allowed, not that not nulls are allowed. Nov 7, 2016 at 22:47
  • 1
    'Update' doesnt work because it returns "0 rows updated" I assume you have something like update A SET City = 'Unknown' WHERE City = NULL? If so, that'll be a duplicate because you can't test for a column being equal to NULL. Instead, you'd need it to be WHERE City IS NULL
    – billinkc
    Nov 7, 2016 at 22:55

1 Answer 1

3

As per the comments, I'm going to assume you're getting mixed up between update and insert.

 USE [DB_A] ;
 UPDATE dbo.address
 SET City = 'test',
     State = 'ing'
 WHERE City IS NULL ;

And

 USE [DB_A] ;
 UPDATE dbo.address
 SET City = NULL,
     State = NULL
 WHERE City = 'test' ;

I think you're there otherwise :-)

3
  • My apologies, I'm not getting the statements confused its just that the 'update' query didn't work for me. It always returns "0 rows updated". I'm not a complete novice with SQL but i'm not an expert by any means. I appreciate your help though so please feel free to fire more ideas my way. All i'm trying to do is change 4000+ rows in 2 columns (city, state) to no longer be null by inserting data into them. 'Update' doesnt work and 'Insert' errors with the "where" clause.
    – L.Moy
    Nov 7, 2016 at 22:05
  • 1
    @L.Moy If the 4000 rows have NULL in the City column, then the 1st UPDATE should work perfectly fine and update all 4000 rows. Of course if there is no NULL there, you'll get "0 rows updated". Are you sure you have NULLs and not empty strings? Nov 7, 2016 at 22:18
  • What does SELECT COUNT(*) FROM dbo.address WHERE city IS NULL; return? Nov 7, 2016 at 22:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.