Update columns within the same table

Hi,

I need to update the first result with the second result of the same table. Can you please help me on this?

-- first Query
-- t2.Access should be updated with t1.Access
SELECT  UserFunctionID,WebMenuID,Access
FROM _WEB_USERFUNCTIONACCESS t2
INNER JOIN
_USERS t4
ON
t2.UserID=t4.UserID
WHERE t4.FunctionAccesssClonedAs='MODELUSER1'

--First result data:
UserFunctionID      WebMenuID      Access
1238      1      1
1239      2      1
1240      3      1
1241      4      1
1242      5      1
1243      6      1
1244      7      1
1245      8      1
1246      9      1
1247      10      1
1248      11      1
1249      12      1
1250      13      1
1251      14      1
1252      15      1
1253      16      1
1254      17      1
1255      19      1
1778      1      1
1779      2      1
1780      3      1
1781      4      1
1782      5      1
1783      6      1
1784      7      1
1785      8      1
1786      9      1
1787      10      1
1788      11      1
1789      12      1
1790      13      1
1791      14      1
1792      15      1
1793      16      1
1794      17      1
1795      19      1
2096      22      1
2097      22      1
2182      23      1
2183      23      1

-- Second  Query
SELECT UserFunctionID,WebMenuID,Access
FROM _WEB_USERFUNCTIONACCESS t1
INNER JOIN
_USERS t3
ON
t1.UserID=t3.UserID
WHERE t3.UserCode='MODELUSER1'

--Second result data:

UserFunctionID      WebMenuID      Access
2446      1      0
2447      2      0
2448      3      0
2449      4      0
2450      5      0
2451      6      1
2452      7      1
2453      8      1
2454      9      1
2455      10      1
2456      11      1
2457      12      1
2458      13      1
2459      14      1
2460      15      1
2461      16      1
2462      17      1
2463      19      1
2464      22      1
2465      23      1
JOLEEJJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It is unclear what your desired end result is.  Can you clarify with the provided sample data if possible?
0
JOLEEJJAuthor Commented:
Hi Brian,

Thanks for taking my question.  When I run the first query, I get the real data in
--First result data:
The second real data comes from running the second query.  Both the queries come from the same table.  What I need to do is update the _WEB_USERFUNCTIONACCESS getting result from the 2nd query to all the rows in the first query.
I hope I have made it clear; otherwise, I'll send the table structure with a script to populate data.
Thanks
Joe
0
Brian CroweDatabase AdministratorCommented:
What specific column is being updated and what is the logic for joining the two sets?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Olaf DoschkeSoftware DeveloperCommented:
SELECT  UserFunctionID,WebMenuID,Access
FROM _WEB_USERFUNCTIONACCESS t2
INNER JOIN
_USERS t4
ON
t2.UserID=t4.UserID
WHERE t4.FunctionAccesssClonedAs='MODELUSER1'

SELECT UserFunctionID,WebMenuID,Access
FROM _WEB_USERFUNCTIONACCESS t1
INNER JOIN
_USERS t3
ON
t1.UserID=t3.UserID
WHERE t3.UserCode='MODELUSER1'

Deductable from these two queries is:
1. The tables _WEB_USERFUNCTIONACCESS and _USERS have a common column "UserID"
2. The table _USERS has columns "UserCode" and "FunctionAccesssClonedAs"

Unclear is, of which table UserFunctionID,WebMenuID,Access are members of.

I'd guess UserFunctionID comes from _USERS, maybe wrong, I can't deduct the other column origins.

A table scheme could be made clear by using full qualified names in the column list, eg prefixing the column names with either t1,t2,t3  and t4 is enough info for that matter.

The verbose results on the other hand are not quite helpful.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JOLEEJJAuthor Commented:
1) The UserFunctionID,WebMenuID,Access are members of  _WEB_USERFUNCTIONACCESS table.

The column to be updated is "Access"

The schema for table "_USERS"

REATE TABLE [dbo].[_USERS](
      [UserID] [smallint] IDENTITY(1,1) NOT NULL,
      [UserCode] [varchar](25) NOT NULL,
      [UserGroup] [varchar](25) NOT NULL,
      [UserFirstname] [varchar](100) NULL,
      [UserLastname] [varchar](100) NULL,
      [Email] [varchar](250) NULL,
      [NetSendID] [varchar](250) NULL,
      [UserType] [varchar](25) NULL,
      [UserActive] [bit] NULL,
      [ContactID] [int] NULL,      
      [FunctionAccesssClonedAs] [varchar](255) NULL,
 CONSTRAINT [PK__USERS] PRIMARY KEY CLUSTERED
(
      [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Unique UserCode] UNIQUE NONCLUSTERED
(
      [UserCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


The schema for table "_WEB_USERFUNCTIONACCESS"

CREATE TABLE [dbo].[_WEB_USERFUNCTIONACCESS](
      [UserFunctionID] [smallint] IDENTITY(1,1) NOT NULL,
      [UserID] [smallint] NULL,
      [WebMenuID] [int] NULL,
      [Access] [bit] NULL,
      [Restricted] [bit] NOT NULL,
      [UpdatedBy] [smallint] NULL,
      [UpdateDtime] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[_WEB_USERFUNCTIONACCESS]  WITH CHECK ADD FOREIGN KEY([UpdatedBy])
REFERENCES [dbo].[_USERS] ([UserID])
GO

ALTER TABLE [dbo].[_WEB_USERFUNCTIONACCESS]  WITH CHECK ADD FOREIGN KEY([UserID])
REFERENCES [dbo].[_USERS] ([UserID])
GO

ALTER TABLE [dbo].[_WEB_USERFUNCTIONACCESS]  WITH CHECK ADD FOREIGN KEY([WebMenuID])
REFERENCES [dbo].[_WEB_MENUS] ([WebMenuID])
GO

ALTER TABLE [dbo].[_WEB_USERFUNCTIONACCESS] ADD  CONSTRAINT [DF__WEB_USERFUNCTIONACCESS_Restricted]  DEFAULT ((0)) FOR [Restricted]
GO
0
PortletPaulfreelancerCommented:
We can create the tables now, but cannot run any queries as there is no data for either table. It will be impossible for us to guess what the actual data looks like and the result is dependent on the data in columns _USERS.UserCode and _USERS.FunctionAccesssClonedAs

Is your current second select query producing the wanted result?
0
PortletPaulfreelancerCommented:
by the way & in my opinion, your convention of using aliases t1, t2, t3, t4 is not helpful

I would suggest you adopt alias naming that hints to the tablename, e.g.

SELECT
      WU.UserFunctionID
    , WU.WebMenuID
    , WU.Access
FROM _WEB_USERFUNCTIONACCESS AS WU
      INNER JOIN _USERS AS U ON WU.UserID = U.UserID
WHERE U.UserCode = 'MODELUSER1'

and, please, do yourself a big favour by including the appropriate alias in ALL column references
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.