Friday, 16 August 2013

Assistance with SQL update

Assistance with SQL update

I hope that I'm making sense with this, but here goes.
What I want to do is take information from two tables (via a JOIN), and
then UPDATE the information from those two tables into a third table. Here
is some test code that I wrote that will mimic almost exactly what I need
to do.
My only problem, is that at the "SELECT * FROM @tempTable" at the
end...I'm unsure of how to get an UPDATE to work on the userName and
badgeId columns within the @tempTable table. Everything I've tried just
ends up inserting it into it's own row, as if I were doing an INSERT...
Any help is appreciated. I wrote and ran the code in MS SQL Server 2008
R2, so it should work.
/* Create the 'Users' table */
DECLARE @Users TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
userName varchar(100),
UserId int)
INSERT INTO @Users (userName, UserId)
VALUES ('jim', 100),
('kira', 200),
('ken', 300),
('dan', 400),
('len', 500)
/* Create the 'Badges' table */
DECLARE @Badges TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
badgeId varchar(100),
userId int)
INSERT INTO @Badges (badgeId, UserId)
VALUES (10, 100),
(20, 200),
(30, 300),
(40, 400),
(50, 500)
/* Create the '@tempTable' */
DECLARE @tempTable TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
userName varchar(100),
badgeId int,
divNumber int,
secNumber int)
INSERT INTO @tempTable (badgeId, userName, divNumber, secNumber)
VALUES (null, null, 68, 34),
(null, null, 68, 34),
(null, null, 69, 24),
(null, null, 69, 24),
(null, null, 70, 14)
/* Select userNames and badgeIds from the 'Users' and 'Badges' tables */
SELECT u.userName, b.badgeId
FROM @Users as u
INNER JOIN @Badges as b
ON b.userId = u.UserId
/* Select all information from the '@tempTable' table */
SELECT * FROM @tempTable

No comments:

Post a Comment