Update query Help - 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

SQL Server Development (2000)

Update query Help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-18 : 14:44:01

[code]
Please help me in modify the below query I need to do is if the import is successfull to tableD
then i want to run the update statement to make the status 'IMP' with 'N'.. in the respectives tables tableA,tableB,TableC
Thanks for your help in advance!!!!!!!

SET dateformat dmy
DECLARE @TableA TABLE
(
Pdate datetime,
SID int,
type int,
Er int,
Ac int,
status char(3)
)
INSERT INTO @TableA
SELECT '7-6-2007', 100, 1, 52, 26,'N' UNION ALL
SELECT '8-6-2007', 100, 1, 52, 47,'N' UNION ALL
SELECT '7-6-2007', 101, 1, 54, 27,'N' UNION ALL
SELECT '8-6-2007', 102, 1, 52, 28,'N' UNION ALL
SELECT '8-6-2007', 105, 1, 62, 38,'N' UNION ALL
SELECT '8-6-2007', 106, 1, 62, 38,'N'

DECLARE @TableB TABLE
(
Pdate datetime,
SID int,
type int,
nt1 varchar(2),
nt2 varchar(3),
status char(3)
)
INSERT INTO @TableB
SELECT '7-6-2007', 100, 2, 'PR', 'RP1','N' UNION ALL
SELECT '8-6-2007', 101, 2, 'PR', 'SP1','N' UNION ALL
SELECT '10-6-2007', 103, 2, '0p', 'DT1','N'

DECLARE @TableC TABLE
(
Pdate datetime,
SID int,
type int,
FT1 varchar(2),
FT2 varchar(2),
status char(3)
)

INSERT INTO @TableC
SELECT '7-6-2007', 100, 3, 'KR', '23','N' UNION ALL
SELECT '7-6-2007', 101, 3, '1R', '21','N' UNION ALL
SELECT '8-6-2007', 102, 3, 'TR', '31','N' UNION ALL
SELECT '9-6-2007', 102, 3, 'TP', '31','N' UNION ALL
SELECT '11-6-2007', 104, 3, 'Rp', 'VT','N'

DECLARE @TableD TABLE
(
Pdate datetime,
SID int,
type int,
Er int,
Ac int,
type2 int,
nt1 varchar(2),
nt2 varchar(3),
type3 int,
FT1 varchar(2),
FT2 varchar(2)

)

INSERT INTO @TableD

SELECT Pdate = a.Pdate,
SID = coalesce(a.SID, b.SID, c.SID),
a_type = a.type, a.Er, a.Ac,
b_type = b.type, b.nt1, b.nt2,
c_type = c.type, c.FT1, c.FT2
FROM (
SELECT Pdate, SID, type, Er, Ac
FROM @TableA t
WHERE Pdate = (SELECT MAX(Pdate) FROM @TableA x WHERE x.SID = t.SID)
and status='N'
AND not exists (select * from @tabled D where D.sid = t.SID)
) a

FULL OUTER JOIN --@TableB b

(
SELECT Pdate, SID, type, nt1, nt2
FROM @Tableb t
WHERE Pdate = (SELECT MAX(Pdate) FROM @TableB x WHERE x.SID = t.SID)
and status='N'
AND not exists (select * from @tabled D where D.sid = t.SID)
) b
ON a.SID = b.SID

FULL OUTER JOIN --@TableC c
(
SELECT Pdate, SID, type, Ft1, Ft2
FROM @Tablec t
WHERE Pdate = (SELECT MAX(Pdate) FROM @TableC x WHERE x.SID = t.SID)
and status='N'
AND not exists (select * from @tabled D where D.sid = t.SID)
) C

ON a.SID = c.SID

ORDER BY SID

/* If the data is imported to table4 from three tables(TablesA,TableB,TableC) then status in three tables of respective records should be updated to 'IMP' from 'N' */

Expected Output:

TableA:

Pdate SID type Er Ac status
------------------------------------------------------ ----------- ----------- ----------- ----------- ------
2007-06-07 00:00:00.000 100 1 52 26 N
2007-06-08 00:00:00.000 100 1 52 47 IMP
2007-06-07 00:00:00.000 101 1 54 27 IMP
2007-06-08 00:00:00.000 102 1 52 28 IMP
2007-06-08 00:00:00.000 105 1 62 38 IMP
2007-06-08 00:00:00.000 106 1 62 38 IMP

TableB:

Pdate SID type nt1 nt2 status
------------------------------------------------------ ----------- ----------- ---- ---- ------
2007-06-07 00:00:00.000 100 2 PR RP1 IMP
2007-06-08 00:00:00.000 101 2 PR SP1 IMP
2007-06-10 00:00:00.000 103 2 0p DT1 IMP

TableC:

Pdate SID type FT1 FT2 status
------------------------------------------------------ ----------- ----------- ---- ---- ------
2007-06-07 00:00:00.000 100 3 KR 23 IMP
2007-06-07 00:00:00.000 101 3 1R 21 IMP
2007-06-08 00:00:00.000 102 3 TR 31 N
2007-06-09 00:00:00.000 102 3 TP 31 IMP
2007-06-11 00:00:00.000 104 3 Rp VT IMP

[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 15:00:00

HEre's an example:


update A
Set Status = 'IMP'
from @TableA A
join @TableD  D ON A.Pdate = D.Pdate And A.SID = D.SID And A.type = d.type And A.Er = d.Er And A.Ac = D.Ac

Similarly, you can update tables B and C and join with appropriate columns.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-18 : 16:47:58

Thanks dinakar....

How should i compare nulls in update query
Ex: A.SID = (D.SID is null or D.SID)

update A
Set Status = 'IMP'
from @TableA A
join @TableD D ON A.Pdate = D.Pdate And A.SID = D.SID And A.type = d.type And A.Er = d.Er And A.Ac = D.Ac

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-19 : 00:08:04

Thanks dinkar!!
Update queries for tableb and tablec are not updating!!!
Please help...


SET dateformat dmy
DECLARE @TableA TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
       status  char(3)
)
INSERT INTO @TableA
SELECT	'7-6-2007', 100, 1, 52, 26,'N' UNION ALL
SELECT	'8-6-2007', 100, 1, 52, 47,'N' UNION ALL
SELECT	'7-6-2007', 101, 1, 54, 27,'N' UNION ALL
SELECT	'8-6-2007', 102, 1, 52, 28,'N' UNION ALL
SELECT	'8-6-2007', 105, 1, 62, 38,'N' UNION ALL
SELECT	'8-6-2007', 106, 1, 62, 38,'N'

DECLARE @TableB TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
status  char(3)
)
INSERT INTO @TableB
SELECT	'7-6-2007', 100, 2, 'PR', 'RP1','N'	UNION ALL
SELECT	'8-6-2007', 101, 2, 'PR', 'SP1','N'	UNION ALL
SELECT	'10-6-2007', 103, 2, '0p', 'DT1','N'

DECLARE @TableC TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	FT1 	varchar(2),
	FT2	varchar(2),
status  char(3)
)

INSERT INTO @TableC
SELECT	'7-6-2007', 100, 3, 'KR', '23','N'	UNION ALL
SELECT	'7-6-2007', 101, 3, '1R', '21','N'	UNION ALL
SELECT	'8-6-2007', 102, 3, 'TR', '31','N'	UNION ALL
SELECT	'9-6-2007', 102, 3, 'TP', '31','N'	UNION ALL
SELECT	'11-6-2007', 104, 3, 'Rp', 'VT','N'


DECLARE @TableD TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
	type2 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
	type3 	int,
	FT1 	varchar(2),
	FT2	varchar(2)


       
)




INSERT INTO @TableD

SELECT	Pdate	= a.Pdate,
	SID	= coalesce(a.SID, b.SID, c.SID),
	a_type	= a.type, 	a.Er, 		a.Ac,
	b_type	= b.type, 	b.nt1, 		b.nt2,
	c_type	= c.type, 	c.FT1, 		c.FT2
FROM	(
		SELECT	Pdate, SID, type, Er, Ac
		FROM	@TableA t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableA x WHERE x.SID = t.SID)
                and status='N'
               AND not exists (select * from @tabled D where D.sid  = t.SID)
	) a 


	FULL OUTER JOIN --@TableB b

(
		SELECT	Pdate, SID, type, nt1, nt2
		FROM	@Tableb t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableB x WHERE x.SID = t.SID)
                and status='N'
                AND not exists (select * from @tabled D where D.sid  = t.SID)
	) b
	ON	a.SID	= b.SID

	FULL OUTER JOIN --@TableC c
(
		SELECT	Pdate, SID, type, Ft1, Ft2
		FROM	@Tablec t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableC x WHERE x.SID = t.SID)
                and status='N'
		AND not exists (select * from @tabled D where D.sid  = t.SID)
	) C


	ON	a.SID	= c.SID

ORDER BY SID

---select * from @tabled


update A
Set Status = 'IMP'
from @TableA A
join @TableD  D ON A.Pdate = D.Pdate And A.SID = D.SID And A.type = d.type And A.Er = d.Er And A.Ac = D.Ac 


update B
Set Status = 'IMP'
from @TableB B
join @TableD  D ON B.Pdate = D.Pdate And B.SID = D.SID And B.type = D.type And B.nt1 = D.nt1 And B.nt2 = D.nt2 


update C
Set Status = 'IMP'
from @TableC C
join @TableD  D ON C.Pdate = D.Pdate And C.SID = D.SID And C.type = d.type And C.Ft1 = d.Ft1 And C.FT2 = D.FT2 


select * from @tablea
select * from @tableb
select * from @tablec

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 00:24:13

Before you do the update, do a SELECT joining the tables B,D and see if you have any records that match. I think the SID's dont match from tables B,D. Check the same for tables C and D too.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-19 : 11:51:12

How should i compare nulls in update query
Ex: C.FT2 = D.FT2

Its seems nulls coomparsioin from tablec and table d having problem..

SID's match from tables B,D and also tables C and D..


SET dateformat dmy
DECLARE @TableA TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
       status  char(3)
)
INSERT INTO @TableA
SELECT	'7-6-2007', 100, 1, 52, 26,'N' UNION ALL
SELECT	'8-6-2007', 100, 1, 52, 47,'N' UNION ALL
SELECT	'7-6-2007', 101, 1, 54, 27,'N' UNION ALL
SELECT	'8-6-2007', 102, 1, 52, 28,'N' UNION ALL
SELECT	'8-6-2007', 105, 1, 62, 38,'N' UNION ALL
SELECT	'8-6-2007', 106, 1, 62, 38,'N'

DECLARE @TableB TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
status  char(3)
)
INSERT INTO @TableB
SELECT	'7-6-2007', 100, 2, 'PR', 'RP1','N'	UNION ALL
SELECT	'8-6-2007', 101, 2, 'PR', 'SP1','N'	UNION ALL
SELECT	'10-6-2007', 103, 2, '0p', 'DT1','N'

DECLARE @TableC TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	FT1 	varchar(2),
	FT2	varchar(2),
status  char(3)
)

INSERT INTO @TableC
SELECT	'7-6-2007', 100, 3, 'KR', '23','N'	UNION ALL
SELECT	'7-6-2007', 101, 3, '1R', '','N'	UNION ALL
SELECT	'8-6-2007', 102, 3, 'TR', '31','N'	UNION ALL
SELECT	'9-6-2007', 102, 3, 'TP', '31','N'	UNION ALL
SELECT	'11-6-2007', 104, 3, 'Rp', 'VT','N'


DECLARE @TableD TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
	type2 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
	type3 	int,
	FT1 	varchar(2),
	FT2	varchar(2)


       
)




INSERT INTO @TableD

SELECT	Pdate	= a.Pdate,
	SID	= coalesce(a.SID, b.SID, c.SID),
	a_type	= a.type, 	a.Er, 		a.Ac,
	b_type	= b.type, 	b.nt1, 		b.nt2,
	c_type	= c.type, 	c.FT1, 		c.FT2
FROM	(
		SELECT	Pdate, SID, type, Er, Ac
		FROM	@TableA t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableA x WHERE x.SID = t.SID)
                and status='N'
               AND not exists (select * from @tabled D where D.sid  = t.SID)
	) a 


	FULL OUTER JOIN --@TableB b

(
		SELECT	Pdate, SID, type, nt1, nt2
		FROM	@Tableb t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableB x WHERE x.SID = t.SID)
                and status='N'
                AND not exists (select * from @tabled D where D.sid  = t.SID)
	) b
	ON	a.SID	= b.SID

	FULL OUTER JOIN --@TableC c
(
		SELECT	Pdate, SID, type, Ft1, Ft2
		FROM	@Tablec t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableC x WHERE x.SID = t.SID)
                and status='N'
		AND not exists (select * from @tabled D where D.sid  = t.SID)
	) C


	ON	a.SID	= c.SID

ORDER BY SID

select * from @tabled

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 12:28:00

Try these 2 SELECts at the bottom of your script:


select * from @tabled order by pdate 
select * from @tableB order by pdate

And observe your data. There is no combination of Pdate and SID values in TableB that match with TableD. Obviously your update will not update anything because there are no matching records.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-19 : 13:10:50

Thanks dinakar!!

I got the desired output...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources