Update ONLY when unique or distinct

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

General SQL Server Forums

New to SQL Server Programming

Update ONLY when unique or distinct

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 16:58:52

Im running updates on a table. I would like to only update the table with new records. Right now, Im running a query that will only update the records that were added yesterday. Here is my code:

FROM GRAB where BKDATE = convert(varchar(8), getdate()-1, 112)

I would like to Update like this (incorrect syntax):

FROM GRAB where FCN is distinct

I know the above is incorrect.... what would be correct?

Thanks again

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 17:08:07

If you update to where FCN is distinct, it will still update all rows where the FCN value is present (even if it is a distinct value)
e.g, if you have 4 rows with a value in a column of "XYZ", the XYZ is a distinct value, but occurs 4 times. If you run an update, all 4 rows will update.

I think by "Distinct" you intend to mean something else

If you want to update based on a date added or other date column you would

Declare @MyDate datetime
Select @MYDate = '11/7/2007'
Update [Table]
Set [Column] = {Value}
Where [DateColumn] = @MYDate

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 17:22:51

if you only want to update records when there is a single record having a specific FCN this may be what you are looking for...

UPDATE GRAB
SET ...
FROM GRAB
JOIN
(
SELECT FCN
FROM GRAB
GROUP BY FCN
HAVING COUNT(*) = 1
) AS FIRST_FCN
ON FIRST.FCN = GRAB.FCN

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 18:02:00

Yes. My FCN Number is unique in the database- it is the Primary Key.

I obviously need to keep it that way, so I only want to update new records. I will try this code:

UPDATE GRAB
SET ...
FROM GRAB
JOIN
(
SELECT FCN
FROM GRAB
GROUP BY FCN
HAVING COUNT(*) = 1
) AS FIRST_FCN
ON FIRST.FCN = GRAB.FCN

Thank you

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 18:10:28

If it is a primary key, then all records will be updated.

To update only NEW records there would have to be some kind of date or other indicator identifying new records.

If GRAB or First is a unique list of the accounts to be updated you can just do:
Update GRAB
Set ...
FROM GRAB inner join FIRST on GRAB.FCN = FIRST.FCN

Don't overcomplicate things. FCN being a primary key means ALL records will have a count of 1 when when grouping by FCN.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 18:44:31

Being a newbie, Im totally confused.

Here is a direct snippit of my code


USE JOD

SET ANSI_WARNINGS OFF

Insert into NAME ("LAST", "FIRST", "MIDDLE", FCN)
SELECT "LAST", "FIRST", MIDDLE, FCN
FROM GRAB where fcn < 'JN4900000' 
AND BKDATE = convert(varchar(8), getdate()-1, 112)

So what would the code be to insert only the records from GRAB that do not already exist in NAME, based on the FCN number, which is the Primary Key.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 18:49:59

Insert into NAME ("LAST", "FIRST", "MIDDLE", FCN)
SELECT "LAST", "FIRST", MIDDLE, FCN
FROM GRAB g
where fcn < 'JN4900000' AND BKDATE = convert(varchar(8), getdate()-1, 112)
and not exists (select * from NAME n where g.FCN = n.FCN)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 20:21:03

quote:
Originally posted by bobshishka

Being a newbie, Im totally confused.

Here is a direct snippit of my code

USE JOD

SET ANSI_WARNINGS OFF

Insert into NAME ("LAST", "FIRST", "MIDDLE", FCN)
SELECT "LAST", "FIRST", MIDDLE, FCN
FROM GRAB where fcn < 'JN4900000'
AND BKDATE = convert(varchar(8), getdate()-1, 112)

So what would the code be to insert only the records from GRAB that do not already exist in NAME, based on the FCN number, which is the Primary Key.

That is a lot different than:

quote:
Originally posted by bobshishka

Im running updates on a table. I would like to only update the table with new records. Right now, Im running a query that will only update the records that were added yesterday. Here is my code:

FROM GRAB where BKDATE = convert(varchar(8), getdate()-1, 112)

I would like to Update like this (incorrect syntax):

FROM GRAB where FCN is distinct

I know the above is incorrect.... what would be correct?

Thanks again

Update means something completely different than the insert (or adding new records). Could have saved a lot of confusion with this being stated earlier:

quote:

So what would the code be to insert only the records from GRAB that do not already exist in NAME, based on the FCN number, which is the Primary Key.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-08 : 11:24:31

Sorry for the confusion.... thats why Im posting in the "New to SQL Server" section.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-08 : 18:05:42

No problem, just future reference. As always, it helps to be very clear, even as a beginner when you are asking for help. Good luck.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-13 : 13:36:24

OK. Update to my issue.

It doesnt seem to be updating when I test adding new records to the database. Here is an example of my code:

Insert into Name ("LAST", "FIRST", "MIDDLE", FCN)
SELECT "LAST", "FIRST", MIDDLE, FCN
FROM GRAB g
where fcn < 'JN4900000'
and not exists (select * from NAME n where g.FCN = n.FCN)

When I run sample data through, even when it is new- it does not update. My record count stays the same. Any ideas???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 13:47:14

You haven't provided enough information for us to help yet. Please post exactly what sample data you are trying to get inserted. Also make sure to let us know what GRAB and FCN look like for this sample row.

BTW, it is always easy enough to just run the SELECT statement by itself rather than with the INSERT. I'd also suggest running it up through the fcn < 'JN4900000' to see if you have rows in GRAB to match that condition. Then remove the fcn < part and just run the not exists. Now run them together. This is how to debug your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-13 : 16:05:12

Every day I get a fixed width text file dumped to my FTP location. I sort out these records and place them in a table called GRAB, that contains all the possible. From there I update 6 other tables. I am trying to only update the records that do not currently exist in the table. That is why this line compares to see if the record currently exists in the table "Name" before it updates the records from "GRAB"

> and not exists (select * from NAME n where g.FCN = n.FCN) <

I removed the line above it (where fcn < 'JN4900000') and I still have the same problem.

Ive created a test text file that contains 1 record that is a duplicate and 1 that has a unique 'FCN' value. When I run the above command, the new record is not added to any of the table. Does that help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 16:09:41

quote:

I am trying to only update the records that do not currently exist in the table.

Are you trying to insert the record or update it? These are different things. Please be very clear so that we don't go down the wrong path like what happened earlier in this thread when you kept saying update and meant insert.

Please post the results of the following queries:

SELECT LAST, FIRST, MIDDLE, FCN
FROM GRAB
where fcn < 'JN4900000'

SELECT LAST, FIRST, MIDDLE, FCN
FROM GRAB g
where not exists (select * from NAME n where g.FCN = n.FCN)

select n.*
from NAME n
inner join grab g
on g.FCN = n.FCN
where g.fcn < 'JN4900000'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-13 : 16:29:15

I want to insert the records from GRAB into Name where they do not already exist.

quote:

SELECT LAST, FIRST, MIDDLE, FCN
FROM GRAB
where fcn < 'JN4900000'

Results= 2 records Test1 and Test2

quote:

SELECT LAST, FIRST, MIDDLE, FCN
FROM GRAB g
where not exists (select * from NAME n where g.FCN = n.FCN)

Results= 0 records

quote:

select n.*
from NAME n
inner join grab g
on g.FCN = n.FCN
where g.fcn < 'JN4900000'

Results= 2 records Test1 and Test2

PS--- thanks...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 16:43:11

Based upon this information, these rows already exist in NAME and GRAB. You'll need to test with sample data that meets your criteria.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources