UPDATE across tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE across tables

Author

Topic

czeller
Starting Member

10 Posts

Posted - 2003-01-21 : 13:02:43

hi everyone, i am trying to "massage" some data and am having a problem w/ a cross table update. here's my ddl:

CREATE TABLE [PW_Users] (
[pkUserID] [int] IDENTITY (10000, 1) NOT NULL ,
[email] [varchar] (75),
[username] [varchar] (50),
[password] [varchar] (30),
[firstName] [varchar] (50),
[lastName] [varchar] (50),
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED
(
[pkUserID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [PW_InfoRequest] (
[pkInfoRequestID] [int] IDENTITY (1, 1) NOT NULL ,
[fkUserID] [int] NOT NULL ,
[active] [bit] NOT NULL,
[dateAdded] [smalldatetime],
CONSTRAINT [PK_PW_InfoRequest] PRIMARY KEY CLUSTERED
(
[pkInfoRequestID]
) ON [PRIMARY]
) ON [PRIMARY]

here's some data:

INSERT INTO PW_Users VALUES ('rjonhson@yahoo.com', NULL, NULL, 'Ron', 'Johnson')
INSERT INTO PW_Users VALUES ('rjonhson@yahoo.com', 'username', 'password', 'Ron', 'Johnson')

INSERT INTO PW_InfoRequest SELECT pkUserID, 1, GETDATE() FROM PW_Users WHERE username IS NULL

INSERT INTO PW_InfoRequest SELECT pkUserID, 1, GETDATE() FROM PW_Users WHERE username IS NULL

INSERT INTO PW_InfoRequest SELECT pkUserID, 1, GETDATE() FROM PW_Users WHERE username IS NULL

so what we've got is rjohnson@yahoo.com w/ two records in the users table, once w/ a login, once w/o a login. i realize it's not an actual "duplicate" record since all the fields don't match, but it is still a problem i need to figure out.

what i want to do is update the info request table's foreign key (fkUserID) w/ the primary key of the user record that has login information (pkUserID). then go thru the user table and delete those duplicated records w/o login info. i can manage the delete query, but i've tried and tried and just can't seem to figure out the update logic involved here. if i join the tables using the fk/pk relationship, i won't be able to get the actual pkUserID i want. the key has to be the email, doesn't it? but in order to get the email for a given user, i HAVE to join the tables which puts me back at getting the incorrect pkUserID???

here's what i've tried (to no avail):

UPDATE PW_InfoRequest
SET fkUserID = U.pkUserID
FROM dbo.PW_Users U
JOIN PW_InfoRequest tmp ON U.pkUserID = tmp.fkUserID
WHERE tmp.fkUserID IN (
SELECT TOP 1 U2.pkUserID
FROM dbo.PW_Users U2
WHERE U.email = U2.email
AND ISNULL(U.username, U.email) = ISNULL(U2.username, U.email)
ORDER BY LEN(U.username) DESC)
AND (
SELECT COUNT(*)
FROM dbo.PW_Users U3
WHERE U.email = U3.email
AND ISNULL(U.username, U.email) = ISNULL(U3.username, U.email)) > 1
AND U.email IS NOT NULL

i've done updates across tables before w/ no problem, but this one has got me puzzled. any suggestions would be greatly appreciated!

thanks!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-21 : 13:18:26


update pw_inforequest
set fkUserid = u2.pkuserid
from
    pw_inforequest ir
    inner join pw_users u
        on ir.fkuserid = u.pkUserid
    inner join pw_users u2
        on (u.email = u2.email and
            u.pkuserid <> u2.pkuserid)
where
    not exists (
        select 1
        from
            pw_users
        where
            email = u2.email and
            pkuserid > u2.pkuserid)

...of course, email is nullable, so this may not work.

You really should look into natural keys, foreign key constraints and check constraints.

Preventing this from happening again is just as important as fixing it.

Jay White
{0}

czeller
Starting Member

10 Posts

Posted - 2003-01-21 : 14:13:25

thank you jay, the update statement and the advice are greatly appreciated. this is a one-time manipulation of this data i was given to clean up in a database redesign project. hopefully the new set up will avoid this type of thing from happening again.

thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources