Updating a single column within a table using multiple tables

Question

Updating a single column within a table using multiple tables

olie480

SSC-Addicted

Points: 443
More actions
March 30, 2010 at 5:04 pm

#222577

Alright SSC. I am just going to start out by saying that I am not a newbie to SQL queries, but I am also not a novice. I know how to update a single column using a query, and other various queries, etc.
But now I have run into a challenge of updating a column in a table, using information from other tables, and I have not been able to get the hang of doing this.
In lamens terms, this is what I would like to do.
I have an 'ExpirationDate', and 'CustomerNumber' column in the table 'Customer' I have another table called 'Points' with columns named 'NonMember' (BOOL), and 'CustomerNumber'(VARCHAR)
Now since I have a psudeo Primary key in both tables ('Customer Number') how do I go about changing the 'NonMember' column to false or true according to the data in the 'Customer' table.
Well, to let you guys know, I have not tried a query yet, until I get the gist of it, but this is how I would try to attempt it.
UPDATE Points SET Points.NonMember = TRUE
WHERE Customer.ExpirationDate < 3/30/2010
AND Customer.CustomerNumber = Points.CustomerNumber;
If anyone could elaborate on this, it may be easier to someone else, but not easy for me.
Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K Cline Ten Centuries Points: 1391 More actions · Answer 1

You're almost there. Just missing the From clause to join the tables. When I have a query with multiple tables I tend to use aliases. Personal preference, it isn't required. Finally, SQL Server doesn't have a boolean data type. To accomplish the same goal I use a Bit.

Update P

Set NonMember = 1

From Customer As C

Inner Join Points As P On P.CustomerNumber = C.CustomerNumber

Where (C.ExpirationDate < '3/30/2010');

WayneS SSC Guru Points: 95460 More actions · Answer 2

K Cline (3/30/2010)
You're almost there. Just missing the From clause to join the tables. When I have a query with multiple tables I tend to use aliases. Personal preference, it isn't required. Finally, SQL Server doesn't have a boolean data type. To accomplish the same goal I use a Bit.
Update P
Set NonMember = 1
From Customer As C
Inner Join Points As P On P.CustomerNumber = C.CustomerNumber
Where (C.ExpirationDate < '3/30/2010');

Actually, the first table in the FROM clause needs to be the table that you are updating, so use:

Update P

Set NonMember = 1

From Points As P

Inner Join Customer As C On P.CustomerNumber = C.CustomerNumber

Where (C.ExpirationDate < '3/30/2010');

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

olie480 SSC-Addicted Points: 443 More actions · Answer 3

Thanks for the help guys, I will definitely try this tomorrow and report back.

I kind of figured it had to do with a join, but like I said, JOIN is not in my SQL vocabulary yet, and I am starting to explore more options within SQL.

Thank you very much again!

olie480 SSC-Addicted Points: 443 More actions · Answer 4

Alright, I just ran the script on the sample database, and everything worked out great! Thanks again for your help. I know that there are many JOIN commands, INNER, OUTER, ETC so I am going to study them to get the hang of them.

The code definitely makes sense to me, just needed some help interpeting what I needed to do.

Thanks again!

K Cline Ten Centuries Points: 1391 More actions · Answer 5

Actually, the first table in the FROM clause needs to be the table that you are updating, so use:

This is not correct. For this process ordering does not matter. It may be a personal preference or some may consider it a best practice, but the updates will work regardless of the order. Proof of concept below.

Declare @Customers Table

(

CustomerId Int Identity(1, 1) Not Null,

MemberSince Date Not Null

);

Declare @UpdateTable Table

(

UpdateId Int Identity(1, 1) Not Null,

CustomerId Int Not Null,

UpdatedValue Bit Not Null

);

Insert Into @Customers (MemberSince)

Values ('1/1/2010'), ('2/1/2010');

Insert Into @UpdateTable (CustomerId, UpdatedValue)

Values (1, 0), (2, 0);

Select *

From @UpdateTable;

-- Updated table first.

Update U

Set UpdatedValue = 1

From @UpdateTable As U

Inner Join @Customers As C On C.CustomerId = U.CustomerId

Where (C.MemberSince = '1/1/2010');

Select *

From @UpdateTable;

-- Updated table second.

Update U

Set UpdatedValue = 1

From @Customers As C

Inner Join @UpdateTable As U On U.CustomerId = C.CustomerId

Where (C.MemberSince = '2/1/2010');

Select *

From @UpdateTable;

Go

Results:

(2 row(s) affected)

UpdateId CustomerId UpdatedValue

----------- ----------- ------------

1 1 0

2 2 0

(2 row(s) affected)

(1 row(s) affected)

UpdateId CustomerId UpdatedValue

----------- ----------- ------------

1 1 1

2 2 0

(2 row(s) affected)

(1 row(s) affected)

UpdateId CustomerId UpdatedValue

----------- ----------- ------------

1 1 1

2 2 1

(2 row(s) affected)