December 31, 2012 at 5:00 am
I have two tables Table1 and Table2 with ID in both tables. I want to update records of First table that matched with second table and also does not match with second Table.
Currently, I am using two Statements to achieve this goal. I want this in one statement.
Below script will explain whole things,
CREATE TABLE [dbo].[Table2](
[ID] [int] NULL,
[Name2] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (1, N'ABC')
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (2, N'CDE')
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (4, N'JKL')
/****** Object: Table [dbo].[Table1] Script Date: 12/31/2012 16:39:43 ******/
CREATE TABLE [dbo].[Table1](
[ID] [int] NULL,
[Name1] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (1, N'ABC')
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (2, N'CDE')
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (3, N'XYZ')
--Updat values in first table that exists in both tables
update T1
set
T1.Name1='abc'
from Table2 T2
inner join Table1 T1 on t1.ID=T2.ID
where T1.ID=1
--Updat values in first table that exists in only first table tables
Update T1
set
T1.Name1='xyz'
from Table2 T2
right outer join Table1 T1 on t1.ID=T2.ID
where T2.ID is null
AND T1.ID=3
I need the updates in one statement instead of Two updates.
Thanks in Advance.
December 31, 2012 at 5:25 am
I think the MERGE statement may work for you here. Have you tried that?
John
December 31, 2012 at 5:43 am
no,
would you please give me any clue of merge.
December 31, 2012 at 6:24 am
azhar.iqbal499 (12/31/2012)
I have two tables Table1 and Table2 with ID in both tables. I want to update records of First table that matched with second table and also does not match with second Table.Currently, I am using two Statements to achieve this goal. I want this in one statement.
Below script will explain whole things,
CREATE TABLE [dbo].[Table2](
[ID] [int] NULL,
[Name2] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (1, N'ABC')
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (2, N'CDE')
INSERT [dbo].[Table2] ([ID], [Name2]) VALUES (4, N'JKL')
/****** Object: Table [dbo].[Table1] Script Date: 12/31/2012 16:39:43 ******/
CREATE TABLE [dbo].[Table1](
[ID] [int] NULL,
[Name1] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (1, N'ABC')
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (2, N'CDE')
INSERT [dbo].[Table1] ([ID], [Name1]) VALUES (3, N'XYZ')
--Updat values in first table that exists in both tables
update T1
set
T1.Name1='abc'
from Table2 T2
inner join Table1 T1 on t1.ID=T2.ID
where T1.ID=1
--Updat values in first table that exists in only first table tables
Update T1
set
T1.Name1='xyz'
from Table2 T2
right outer join Table1 T1 on t1.ID=T2.ID
where T2.ID is null
AND T1.ID=3
I need the updates in one statement instead of Two updates.
Thanks in Advance.
The CASE statement can help:
UPDATE
dbo.Table1
SET
Name1 = CASE WHEN T2.ID IS NULL THEN 'XYZ' ELSE 'ABC' END
FROM
dbo.Table1 T1
LEFT OUTER JOIN
dbo.Table2 T2
ON
T1.ID = T2.ID;
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
December 31, 2012 at 6:46 am
That code wasn't quite right:
UPDATE
dbo.Table1
SET
Name1 = CASE
WHEN (T2.ID IS NULL AND T1.ID = 3) THEN 'XYZ'
WHEN T1.ID = 3 THEN 'ABC'
ELSE Name1 END
FROM
dbo.Table1 T1
LEFT OUTER JOIN
dbo.Table2 T2
ON
T1.ID = T2.ID;
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
December 31, 2012 at 7:14 am
Roland, I tweaked your query a little to include a WHERE clause & was consequently able to simplify the CASE construct. Also, your update needed to reference the alias (t1) rather than the table name:
update T1
set Name1 = case
when T2.ID is null
then 'XYZ'
else 'ABC'
end
from dbo.Table1 T1
left join dbo.Table2 T2 on T1.ID = T2.ID
where (
t1.Id = 1
and t2.id is not null
)
or (
t1.id = 3
and t2.id is null
)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 31, 2012 at 7:28 am
Nicely done, Phil, thanks!
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply