complicated (to me) table update

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

Transact-SQL (2000)

complicated (to me) table update

Author

Topic

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 14:15:48

hi all, i'm new to these forums but have a decent amount of experience using basic to advanced sql. however, i've been out of practice and am finding this problem to be too much for me. so, here goes...

problem:

i need a query to update a date field (dateA) of tableA from the larger of dateBx or dateBy of tableB. both tables have a common field (ident).

i use a query to get the rows in tableA that i want to update. here is the query:

select tableA.ident
from tableA, tableB
where tableA.ident = tableB.ident

i think i need to loop through the ident field of this query, set it to a variable, then use that variable as part of the where clause in the update, that i may or may not need to loop.

i'm not real sure of the syntax for what i need to do, so if anyone can give me a hand with this, i would appreciate it.

thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 14:18:24

Please provide DDL (CREATE TABLE statements for both tables), DML (INSERT INTO statements for sample data for both tables), and the expected result set using the sample data.

You're probably going to need an UPDATE with a JOIN.

Tara

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 15:08:26

I hope this is what you need. The tables are fairly large, so I removed extraneous fields.

CREATE TABLE [dbo].[OPW] (
[SSAN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToAFPCDateTime] [datetime] NULL ,
[IsFinal] [int] NOT NULL ,
[DTFinal] [bit] NOT NULL ,
[DTReviewDt] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[VML] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ssan] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cycleid] [int] NULL ,
[OPWdt] [datetime] NULL
) ON [PRIMARY]
GO

=====================================================================

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('217108656','2003-02-01 17:57:44.000',NULL)

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('327202358','2004-01-14 07:41:13.000','2004-03-04 13:51:42.257')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('486706882','2003-06-30 12:46:25.000',NULL)

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('335854888','2004-01-07 23:31:32.000','2004-01-09 11:03:19.003')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('658761438','2003-06-30 12:46:25.000','2004-01-07 23:31:32.000')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('456891286','2003-06-30 12:46:25.000',NULL)

=====================================================================

insert into vml
(ssan, cycleid, OPWdt)
values
('217108656', 21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('327202358', 21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('486706882',21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('335854888',21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
(269697484,21,NULL)

==================================================================
expected results:

select *
from vml

ssan cycleid OPWdt
--------- ------- -----------------------
217108656 21 2003-02-01 17:57:44.000
327202358 21 2004-03-04 13:51:42.257
486706882 21 2003-06-30 12:46:25.000
335854888 21 2004-01-09 11:03:19.003
269697484 21 2003-02-01 17:57:44.000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 15:21:57

Do you need to UPDATE the VML table or can you just use the result set like a table:



SELECT 	v.ssan, 
	cycleid, 
	OPWdt =	CASE 
			WHEN dtreviewdt >= toafpcdatetime THEN dtreviewdt 
			ELSE toafpcdatetime 
		END
FROM 	vml v
INNER JOIN opw o 
ON 	v.ssan = o.ssan

Tara

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 15:23:26

I need to update it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 15:25:03

Here's the UPDATE statement:



UPDATE v
SET OPWdt = 	CASE 
			WHEN dtreviewdt >= toafpcdatetime THEN dtreviewdt 
			ELSE toafpcdatetime 
		END
FROM 	vml v
INNER JOIN opw o 
ON 	v.ssan = o.ssan

There was no corresponding OPW row for ssan = 269697484 in your sample data, so this is what vml looks like after the UPDATE:



id          ssan      cycleid     OPWdt                                                  
----------- --------- ----------- ------------------------------------------------------ 
1           217108656 21          2003-02-01 17:57:44.000
2           327202358 21          2004-03-04 13:51:42.257
3           486706882 21          2003-06-30 12:46:25.000
4           335854888 21          2004-01-09 11:03:19.003
5           269697484 21          NULL

It doesn't match your expected result set due to the sample data. But I think this is what you want with real data.

Tara

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 15:30:42

Wow, that's some pretty powerful stuff! Thanks!

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 15:43:40

Would this work with this added:

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('327202358','2004-03-17 10:13:32.000',NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 15:50:04

I'm not sure what you mean. To get my result set equal to yours? I'd need ssan = 269697484 have a related row in the opw table. So if you change your INSERT from 327202358 to 269697484, then yes the UPDATE will show what you need. Have you tried the query on your real data?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 15:50:52

Here's the code that I worked with:



set nocount on

CREATE TABLE [dbo].[OPW] (
[SSAN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToAFPCDateTime] [datetime] NULL ,
-- [IsFinal] [int] NOT NULL ,
-- [DTFinal] [bit] NOT NULL ,
[DTReviewDt] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VML] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ssan] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cycleid] [int] NULL ,
[OPWdt] [datetime] NULL 
) ON [PRIMARY]
GO

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('217108656','2003-02-01 17:57:44.000',NULL)

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('327202358','2004-01-14 07:41:13.000','2004-03-04 13:51:42.257')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('486706882','2003-06-30 12:46:25.000',NULL)

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('335854888','2004-01-07 23:31:32.000','2004-01-09 11:03:19.003')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('658761438','2003-06-30 12:46:25.000','2004-01-07 23:31:32.000')

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('456891286','2003-06-30 12:46:25.000',NULL)

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('269697484','2004-03-17 10:13:32.000',NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('217108656', 21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('327202358', 21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('486706882',21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
('335854888',21,NULL)

insert into vml
(ssan, cycleid, OPWdt)
values
(269697484,21,NULL)

UPDATE v
SET OPWdt = 	CASE 
			WHEN dtreviewdt >= toafpcdatetime THEN dtreviewdt 
			ELSE toafpcdatetime 
		END
FROM 	vml v
INNER JOIN opw o 
ON 	v.ssan = o.ssan

SELECT *
FROM vml

drop table vml
drop table opw

Run it on a different database in case you already have these two tables.

Tara

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 15:59:00

If there is another record in opw for 327202358, or any ssan for that matter, would the greater of toafpcdatetime and dtreviewdt for both records be the one that populated opwdt? In other words, just for 327202358, would the results look like this:

id ssan cycleid OPWdt
----------- --------- ----------- ------------------------------------------------------
2 327202358 21 2004-03-17 10:13:32.000

with data in OPW looking like this:

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('327202358','2004-01-14 07:41:13.000','2004-03-04 13:51:42.257')

and

insert into opw
(ssan, toafpcdatetime, DTreviewdt)
values
('327202358','2004-03-17 10:13:32.000',NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 16:01:33

Yes, that's how it is coded. It says whichever one is greater, put in OPWdt. I'm not sure the data that you just showed though. 2004-03-17 10:13:32.000 does not appear in OPW. But '2004-03-04 13:51:42.257 would appear in OPWdt since it is greater than 2004-01-14 07:41:13.000.

Tara

jones
Starting Member

8 Posts

Posted - 2004-03-25 : 16:06:07

Phwew!

That's good to know. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources