March 30, 2010 at 5:04 pm
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!
March 30, 2010 at 5:46 pm
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');
March 30, 2010 at 7:31 pm
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
March 30, 2010 at 8:08 pm
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!
March 31, 2010 at 9:43 am
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!
March 31, 2010 at 12:55 pm
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)
(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)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy