Update SQL help needed *solved!*

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)

Update SQL help needed *solved!*

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 13:05:45

Hi everyone!

I have two tables. Table A has records I want to read and then insert/update into Table B. There is a flag in Table A called Processed. It is set to a 'P' on the back end.

Insert - Only distinct records can be inserted. There may be 20 records in TableA for a person, but there should only be one record in TableB for that person.

Update - Use the record from TableA that is newer than the one in TableB and update the record in TableB.

My dilemma is that there are currently zero recs in TableB. I need to set the flag to 'X' for the records that I want to insert.

Here is the SQL I have so far but it's selecting all of the records to insert and will create dup recs in TableB. How do I tell it 'distinct'?

update QIKTRAN
set PROCESSED = 'X'
where (PROCESSED = 'P' and (PRIMSSN + DIRSTATE) NOT IN (SELECT PRIMSSN + DIRSTATE FROM QIKLOOK))

Thank you for your continued assistance!
Teresa

"Happiness if found along the way;
not at the end of the road."

Edited by - tj on 02/21/2002 13:08:12

Edited by - tj on 02/21/2002 17:13:45

Edited by - tj on 02/22/2002 13:00:23

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-21 : 13:22:31

I may be missing something here, but try this: (SELECT DISTINCT instead of just SELECT)

update QIKTRAN
set PROCESSED = 'X'
where (PROCESSED = 'P' and (PRIMSSN + DIRSTATE) NOT IN (SELECT DISTINCT PRIMSSN + DIRSTATE FROM QIKLOOK))

Michael

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 13:33:48

Michael

That code tested for the distinct recs in Table B and set all of the recs in Table A to 'X'. I need it to work the other way around and look for the distinct recs in Table A.

Thanks for trying! :)

Teresa

"Happiness is found along the way;
not at the end of the road."

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 14:05:27

Does anyone think a self join might work?

I've been trying to get this code to work:

update t1
set PROCESSED = 'X'
FROM
QIKTRAN t1
INNER JOIN
(select distinct primssn + dirstate from qiktran t2
where t2.primssn + t2.dirstate is not null)
as t2 ON t1.primssn + t1.dirstate <> t2.primssn + t2.dirstate
where t1.processed = 'P' and t2.processed = 'P'

I keep getting an error that says:
No column was specified for column 1 of 't2'

I can't figure out what it's looking for??

I know a cursor would do the trick, but dog-gone-it... I just don't want to use one!

Thanks!

"Happiness is found along the way;
not at the end of the road."

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-21 : 14:52:58

Try this

update t1
set PROCESSED = 'X'
FROM
QIKTRAN t1
INNER JOIN
(select distinct primssn + dirstate as t2_sum from qiktran t2
where t2.primssn + t2.dirstate is not null)
as t2 ON t1.primssn + t1.dirstate <> t2.primssn + t2.dirstate
where t1.processed = 'P' and t2.processed = 'P'

I believe it just wants you to assign a column name to
primssn + dirstate, but I may be wrong

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 16:40:18

Tigger,

That's what it was looking for. Thank you.

However, this doesn't work for me either.

Does anyone have any other idea? I'm working with a temp table now. I'll see what happens with that.

Thanks again!
Teresa

"Happiness is found along the way;
not at the end of the road."

izaltsman
A custom title

1139 Posts

Posted - 2002-02-21 : 17:42:55

Teresa,

I'm a bit confused by your explanation... So I'll try to re-state the problem, and you can correct me if I'm missing something. Once we get the requirements cleared up, we should be able to solve it. So here goes:

Your QIKTRAN table contains information about multiple people. Each person can have multiple records associated with him/her. You want to take one record for each person and update QIKTRAN.PROCESSED field to 'X'. Does that sound right?

So for each person, how do you determine which row to update? Do you just want to take a arbitrary row? Or the decision based on some field?

What are the unique/primary keys on this table?

Edited by - izaltsman on 02/21/2002 17:44:22

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 17:51:59

quote:

Your QIKTRAN table contains information about multiple people. Each person can have multiple records associated with him/her. You want to take one record for each person and update QIKTRAN.PROCESSED field to 'X'. Does that sound right?

Correct QIKTRAN contains multiple records for each person. I need only one rec to be inserted into QIKLOOK.

quote:

So for each person, how do you determine which row to update? Do you just want to take a arbitrary row? Or the decision based on some field?

This is where I'm stuck. I need to select distinct records based on the PRIMSSN and DIRSTATE fields. Any other records that meet this criteria will need to be handled later in an update clause.

quote:

What are the unique/primary keys on this table?

There in lies the ruff! There aren't any and none can be created because of duplication. I created an index on the primssn & dirstate fields. That is the only thing I can do. The QIKLOOK may end up with two records but they will be distinct records. One will be from the IRS return and the other from their State return with each being updated with applicable info from the QIKTRAN table.

Thank you so much for responding.
Teresa

"Happiness is found along the way;
not at the end of the road."

izaltsman
A custom title

1139 Posts

Posted - 2002-02-21 : 18:18:17

quote:

There aren't any and none can be created because of duplication. I created an index on the primssn & dirstate fields. That is the only thing I can do.

Can you change the table structure? 'Cause if you added something like an identity column (at least temporarily), it would give us a nice surrogate key, which would make the life a LOT easier.

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 18:23:22

quote:

Can you change the table structure? 'Cause if you added something like an identity column (at least temporarily), it would give us a nice surrogate key, which would make the life a LOT easier.

How would it work if I created a temp table with the primssn, dirstate, and a surrogate key. Would it be possible to work with it this way? I would still need to get the processed status changed in QIKTRAN though.

Thanks,
Teresa

"Happiness is found along the way;
not at the end of the road."

izaltsman
A custom title

1139 Posts

Posted - 2002-02-21 : 18:49:47

If this is a one-time deal, then you can create a temp table, do the update, truncate original table and copy the results back (see code below). But if you are planning to do this on the regular basis, then you should re-design your data model.


SELECT IDENTITY(int, 1,1) as idfld, * INTO #temp 
FROM qiktran 
WHERE 1=2

INSERT INTO #temp
SELECT * FROM qiktran 

UPDATE t
SET t.processed = 'X'
FROM 
	#temp t 
	INNER JOIN 
	(SELECT primssn, dirstate, MIN(idfld) as idfld FROM #temp GROUP BY primssn, dirstate) as t2
		ON t.primssn = t2.primssn AND t.dirstate = t2.dirstate AND t.idfld = t2.idfld


TRUNCATE TABLE qiktran

INSERT INTO qiktran (primssn, dirstate, processed)
SELECT primssn, dirstate, processed FROM #temp

drop table #temp

Edited by - izaltsman on 02/21/2002 18:50:37

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-21 : 19:02:58

quote:

If this is a one-time deal, then you can create a temp table, do the update, truncate original table and copy the results back (see code below). But if you are planning to do this on the regular basis, then you should re-design your data model.

This will need to be done every morning.

I believe I can add a column. I'll verify that it won't change anything that occurs in the dts, but will allow me some capabilities to further work with the table.

What would you suggest I do. Better said, what would you do if it were you?

"Happiness is found along the way;
not at the end of the road."

izaltsman
A custom title

1139 Posts

Posted - 2002-02-22 : 08:34:10

Ideally, I would recommend going through your entire data model and making some serious changes 'cause I can bet this isn't the only poorly designed table...
If that DTS package you mentioned is doing a bulk load into QIKTRAN it might be better to keep QIKTRAN as a "staging" table, and then move the loaded data into a more normalized data structure. But since you already have an application built on top of this database, you may not have the luxury of a major overhaul.
So as it stands right now, I would add an identity column to QIKTRAN table, and implement an update statement similar to the one I showed for a temp table. Since you are going to be doing this on a regular basis, you are going to have to join to the QIKLOOK table, to make sure your query only works on the records that don't yet exist in there. So the query would probably look something like this:


UPDATE t
SET t.processed = 'X'
FROM 	qiktran t 	
	INNER JOIN 	
	(SELECT primssn, dirstate, MIN(idfld) as idfld 
	 FROM qiktran 
         GROUP BY primssn, dirstate) as t2		
		ON t.primssn = t2.primssn AND t.dirstate = t2.dirstate AND t.idfld = t2.idfld
	LEFT JOIN qiklook l
		ON t.primssn = l.primssn AND t.dirstate = l.dirstate
WHERE 
	l.primssn IS NULL

HTH

Edited by - izaltsman on 02/22/2002 08:40:01

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-22 : 09:30:17

You're right. It's not the only table in this kind of shape. I do have the luxury of using it as a staging table. Most of the tables that are like this are dBaseIII data dumps. There are three of us who try to figure out what to do with them when we need data. None of us are DBA's.

We're going to do an overhaul of some of these tables summer. Hopefully something we come up with will be better than what we have!

I'm going to give your code a try this morning.

Thanks again!
Teresa

Edited by - tj on 02/22/2002 09:35:38

TJ
Posting Yak Master

201 Posts

Posted - 2002-02-22 : 12:59:40

An FYI to all who view this thread....

This code worked and took a difficult process and simplified it!

Thank you ISALTZMAN! You have been my saving grace!

Best regards,
Teresa

"Happiness is found along the way;
not at the end of the road."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources