Joseph Jones
asked on
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,A ccess
FROM _WEB_USERFUNCTIONACCESS t2
INNER JOIN
_USERS t4
ON
t2.UserID=t4.UserID
WHERE t4.FunctionAccesssClonedAs ='MODELUSE R1'
--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,A ccess
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
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,A
FROM _WEB_USERFUNCTIONACCESS t2
INNER JOIN
_USERS t4
ON
t2.UserID=t4.UserID
WHERE t4.FunctionAccesssClonedAs
--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,A
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
It is unclear what your desired end result is. Can you clarify with the provided sample data if possible?
ASKER
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
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
What specific column is being updated and what is the logic for joining the two sets?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1) The UserFunctionID,WebMenuID,A ccess 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_USERFUNCTIONAC CESS](
[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_USERFUNCTIONAC CESS] WITH CHECK ADD FOREIGN KEY([UpdatedBy])
REFERENCES [dbo].[_USERS] ([UserID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC CESS] WITH CHECK ADD FOREIGN KEY([UserID])
REFERENCES [dbo].[_USERS] ([UserID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC CESS] WITH CHECK ADD FOREIGN KEY([WebMenuID])
REFERENCES [dbo].[_WEB_MENUS] ([WebMenuID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC CESS] ADD CONSTRAINT [DF__WEB_USERFUNCTIONACCES S_Restrict ed] DEFAULT ((0)) FOR [Restricted]
GO
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_USERFUNCTIONAC
[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_USERFUNCTIONAC
REFERENCES [dbo].[_USERS] ([UserID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC
REFERENCES [dbo].[_USERS] ([UserID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC
REFERENCES [dbo].[_WEB_MENUS] ([WebMenuID])
GO
ALTER TABLE [dbo].[_WEB_USERFUNCTIONAC
GO
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.FunctionAccesssClon edAs
Is your current second select query producing the wanted result?
Is your current second select query producing the wanted result?
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
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