Help on creating a query to update some records.

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)

Help on creating a query to update some records.

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-06-21 : 16:46:30

I am looking for an easy way to update a date column row by row.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[iso_employwi]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[iso_employwi]

GO

CREATE TABLE [dbo].[iso_employwi] (
	[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[pin] [int] NOT NULL ,
	[wi] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[date_added] [smalldatetime] NULL ,
	[date_trained] [smalldatetime] NULL ,
	[wi_title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[rev_date] [smalldatetime] NULL 
) ON [PRIMARY]

INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('001','WI46','C','C','6/05/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,date_trained,rev_date)
VALUES ('001','WI95','B','B','6/10/2005','6/05/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('002','WI451','A','A','6/18/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('002','WI462','B','B','6/19/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('003','WI478','B','A','6/02/2005')

GO

I would like the date from the 'rev_date' to be used to populate 'date_trained'. When certain criteria is met of course.
Below is a sample view of the criteria results before updating:


SELECT     pin, wi, rev_date, date_trained
FROM         dbo.iso_employwi
WHERE     (rev = level_trained)
GROUP BY pin, wi, rev_date, date_trained
HAVING      (date_trained IS NULL)

I would like the 'date_trained' to be populated based on each individual pin/wi. For example, after the updating is done,
the record results should look like this:

1 | WI46 | 2005-06-05 | 2005-06-05
2 | WI451 | 2005-06-18 | 2005-06-18
2 | WI462 | 2005-06-19 | 2005-06-19

This query will only be ran once to update those missing dates.

Thanks for your help!

JLM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 17:14:33

UPDATE iso_employwi
SET date_trained = rev_date
WHERE date_trained IS NULL

Tara

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-06-24 : 14:07:55

Thank you Tara, that worked great!

Can you help me on another query (Insert).

Take the same table created from above. There are a total of 5 records;
two records for pin # 1, two records for pin 2 and 1 record for pin 3.
What I would like to do is insert 1 new record for each grouped pin. So in other
words, there will be three new records created in this table which will then
make a total of 8 records. Pin 1, 2 & 3 will each have a new 'WI' record added
to this table. 'WI7-1-001'

When I run this query on the actual table, I do want it to go through all the pins (grouping the same ones) and inserting a new record.

Does this make sense?

JLM

(Thanks again!)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-24 : 15:21:59

insert into iso_employwi (pin, wi)
select distinct pin, 'WI7-1-001'
from iso_employwi

Tara

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-07-11 : 16:27:16

Thx again Tara!

I have another question/query that I need help with.

Say there are a few users that already have the 'wi' in question, before I mass update the records.
I would like to update those that don't already have the 'wi' in their record listing so as they don't
end up with duplicate entries.
Your query:

insert into iso_employwi (pin, wi)
select distinct pin, 'WI7-1-001'
from iso_employwi

How can I modify this query to do the following steps:

1) Select distinct pin
2) Check to see if the 'wi' is already there first, if so, then disregard this pin
3) update the pins with the 'wi'

And again, thanks so much Tara!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-11 : 18:23:49

Just add a WHERE clause.

Tara

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-07-11 : 19:06:01

I did try that first.


INSERT INTO iso_employwi (pin, wi)
SELECT DISTINCT pin, 'QM1-1-1'
FROM iso_employwi
WHERE (iso_employwi.wi <> 'QM1-1-1')

But it didn't work. The problem is that in the table 'iso_employwi', a user can have multiple records with different 'wi' entries.
For example, a users records may look like this:

PIN | WI | ETC
2 | QM1-1-1 | blabla
2 | QM3-6-3 | blabla
2 | QM6-3-3 | blabla

When I run the code above, it disregards any record that has the 'wi' mentioned, but since there are two other entries for the pin # '2', it still selects that pin and adds a new record. Does this make sense?

Here is a table example with some data:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[iso_employwi]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[iso_employwi]

GO

CREATE TABLE [dbo].[iso_employwi] (
	[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[pin] [int] NOT NULL ,
	[wi] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[date_added] [smalldatetime] NULL ,
	[date_trained] [smalldatetime] NULL ,
	[wi_title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[rev_date] [smalldatetime] NULL 
) ON [PRIMARY]

INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('002','QM1-1-1','C','C','6/05/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,date_trained,rev_date)
VALUES ('002','WI95','B','B','6/10/2005','6/05/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('002','WI451','A','A','6/18/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('003','WI462','B','B','6/19/2005')
INSERT INTO iso_employwi (pin,wi,rev,level_trained,rev_date)
VALUES ('004','WI478','B','A','6/02/2005')

GO

amarmano
Starting Member

3 Posts

Posted - 2005-07-12 : 05:58:18

just try this, hope it works

INSERT INTO iso_employwi (pin, wi)
SELECT DISTINCT pin, 'QM1-1-1'
FROM iso_employwi
WHERE pin NOT IN
(SELECT DISTINCT pin
FROM iso_employwi
WHERE (iso_employwi.wi = 'QM1-1-1'))

Amar

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-07-12 : 10:33:10

That worked great! Thx Amar

JLM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources