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,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

Brian Crowe

It is unclear what your desired end result is. Can you clarify with the provided sample data if possible?

Joseph Jones

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

Brian Crowe

What specific column is being updated and what is the logic for joining the two sets?

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joseph Jones

ASKER

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

PortletPaul

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?

PortletPaul

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