[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

Update Statement for new Column

I'm sure this is an easy one for you database gurus :)

I added Column3 to existing Table1 as a FK, which is the PK for a new Table5 I added.  When I added the column to Table1, it populated the values as NULL, so it won't let me INSERT values (I normally do a BULK INSERT from CSV).

I am obviously not a database expert :), so what is the simplest way to update the NULL values to a list of real values?  If an advanced statement is needed, great, but please start simple to help me understand how/why.

2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, any chance you can use real table and column names?  Column3, Table1, Table5 makes it hard to conceptualize.

>so what is the simplest way to update the NULL values to a list of real values?
Spell out for us what other columns you would use to relate between this table, and the one you're trying to populate from, so that it knows what value to add.

If you can't do that, then you have no way to update the values for the current records, which means you'd have to delete all rows, then re-populate with the added column.
Reza RadCommented:
as the Column3 pointing to Table5, so you can only set values in Column3 that exists in Table5.
So how you want to update it? I mean what is logic for that update?
how you know that each record in Table1 points to which record in Table5 and then set Column3's values accordingly?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think this article will be helpful to do a UPDATE with JOIN, presuming the values are something you can query from another table?

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

PowerEdgeTechIT ConsultantAuthor Commented:
Yeah, no problem:

   AthleteID int PRIMARY KEY IDENTITY(1,1),
   FirstName varchar(50),
   LastName varchar(50),
   Gender varchar(1)

Populated Athlete.

   SchoolID int PRIMARY KEY IDENTITY(1,1),
   Name varchar(50),
   City varchar(50),
   Class varchar(2),
   Region varchar(2)

Populated School.


Now need to populate the new NULL values in Athlete.SchoolID with values from School.SchoolID.  

To be honest, this is a "fake" database I'm trying to put together for learning/testing a website idea, so the Athlete.SchoolID needs only be populated using random values from School.SchoolID.  I figured I could certainly drop the table and recreate it with the new column and values easy enough, but I'm trying to learn a little as I go, and surely there is a good/right way to do what I'm trying to do.

I tried the following:

CREATE TABLE tempAthleteSchool (
   AthleteID int,
   SchoolID int

Did a BULK INSERT from CSV to populate the Athlete ID with values from Athlete.AthleteID and random values from School.SchoolID.  Then I tried to update the Athlete table from the tempAthleteSchool table:

UPDATE Athlete
SET SchoolID = tempAthleteSchool.SchoolID
WHERE AthleteID = tempAthleteSchool.AthleteID

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "tempAthleteSchool.AthleteID" could not be bound.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work better:
SET SchoolID = t.SchoolID
FROM Athlete a
JOIN tempAthleteSchool t
 ON a.AthleteID = t.AthleteID

Open in new window

or this one, though "less readable" in my opinion
UPDATE Athlete
SET SchoolID = tempAthleteSchool.SchoolID
FROM tempAthleteSchool
WHERE AthleteID = tempAthleteSchool.AthleteID

Open in new window

PowerEdgeTechIT ConsultantAuthor Commented:
That is perfect.  I used the second one, as I am better able to see what it is doing and is the most similar to what I was trying to do - the syntax is a little different than I expected, but I get it.  I need to dive more into joins, I realize, since I didn't really follow the top one.

So, is there a better way you would recommend doing this?  For example, is there a way to create the new column and insert values in the same step?  Or is using a temp table kind of the way to go?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>For example, is there a way to create the new column and insert values in the same step?
not possible

you could have another syntax for the update (using subquery), but in the end you cannot create the column AND add data to it in the same step
PowerEdgeTechIT ConsultantAuthor Commented:
Fair enough :)  Thank you!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now