SELECT & UPDATE - 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

Transact-SQL (2000)

SELECT & UPDATE

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-09-16 : 16:35:41

Will someone please tell me why the select works and the update does not?


SELECT CUSTNUMBER
FROM EFILE
WHERE NOT EXISTS
(SELECT CUSTNUMBER
	FROM DLRCODES
	WHERE CUSTNUMBER = EFILE.CUSTNUMBER)

UPDATE DLRCODES
SET DLRCODES.CUSTNUMBER = EFILE.CUSTNUMBER
WHERE NOT EXISTS
(SELECT EFILE.CUSTNUMBER 
        FROM EFILE
        WHERE DLRCODES.CUSTNUMBER = EFILE.CUSTNUMBER)

I know I'm missing something, but can't figure out what it is!

Can I do a 'not exists' in an update?

Thanks!

Teresa

Edited by - TJ on 09/16/2002 17:49:22

lozitskiy
Starting Member

28 Posts

Posted - 2002-09-16 : 18:59:15

UPDATE DLRCODES
SET DLRCODES.CUSTNUMBER = EFILE.CUSTNUMBER
WHERE NOT EXISTS
(SELECT EFILE.CUSTNUMBER
FROM EFILE
WHERE DLRCODES.CUSTNUMBER = EFILE.CUSTNUMBER)

1. There is no join ot EFILE.
2. SET DLRCODES.CUSTNUMBER muset be SET DLRCODES.CUSTNUMBER
3. Even if you correct this - every record for DLRCODES will be updated - try to guess why...

-------------
MCP MSSQL

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-16 : 19:03:35

Because EFILE is only available in the subquery not in the update.

Any way you are trying to set DLRCODES.CUSTNUMBER to a value which doesn't exist (I guess).

UPDATE DLRCODES
SET DLRCODES.CUSTNUMBER = null
WHERE NOT EXISTS
(SELECT *
FROM EFILE
WHERE DLRCODES.CUSTNUMBER = EFILE.CUSTNUMBER)

will work and I guess is what you want.
(Never like to see anything other than "select *" in an existence check.)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-16 : 19:08:21

>> 3. Even if you correct this - every record for DLRCODES will be updated - try to guess why...

Nope - only those recs which don't have a corresponding entry in EFILE.

maybe what you want is
insert DLRCODES
SELECT CUSTNUMBER
FROM EFILE
WHERE NOT EXISTS
(SELECT CUSTNUMBER
FROM DLRCODES
WHERE CUSTNUMBER = EFILE.CUSTNUMBER)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-16 : 19:27:48

quote:

Never like to see anything other than "select *" in an existence check.

I never like to see anything by "Select 1" in an existence check.

DavidM

"SQL-3 is an abomination.."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-16 : 19:53:17

It's funny you mention that, Joe Celko has the following in SQL Puzzles & Answers:

quote:
The use of a constant in the SELECT clause instead of an asterisk is a good trick, but it depends on your SQL. Some optimizers will see that this is an EXISTS() predicate, which means that the system can replace the asterisk with one or more columns of its own choice. The optimizer then looks at which columns in the WHERE clause have indexes, and uses the best indexes for the job.

Some quick testing in SQL Server tends to show that it doesn't seem to make any difference in the execution plans (not in pubs or Northwind anyway) But I'm more inclined to think that SELECT * would be better if there are other tests in the WHERE clause besides the join conditions.

TJ
Posting Yak Master

201 Posts

Posted - 2002-09-16 : 19:57:32

quote:

>> 3. Even if you correct this - every record for DLRCODES will be updated - try to guess why...

Nope - only those recs which don't have a corresponding entry in EFILE.

That's exactly what happened. Not what I expected at all!

quote:

maybe what you want is
insert DLRCODES
SELECT CUSTNUMBER
FROM EFILE
WHERE NOT EXISTS
(SELECT CUSTNUMBER
FROM DLRCODES
WHERE CUSTNUMBER = EFILE.CUSTNUMBER)

Forgive me... but I'm at home and do not have access to test this code before asking questions....

Will the 'insert' only insert the data or a new record? I know that insert into, inserts a record, but haven't seen the insert used alone.

Thanks for responding! I can't wait to try it tomorrow morning!

Best regards,
Teresa

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-16 : 20:40:34

Sorry to go off-topic on you TJ...

Rob,

The reason I like the "1" instead of a "*" is more personal than anything else. 2 reasons..

1) I mentally associate the EXISTS operator with a BOOLEAN expression.... although the implementation of a proper BOOLEAN data type has never existed in SQL, (a horrifying thought considering that DR Codd said that the only data type needed for a relational system is a BOOLEAN!).. anyway the "1" signifies to me TRUE

2) The resulting columns are un-important

"Different folks, different strokes.."

DavidM

"SQL-3 is an abomination.."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-17 : 02:55:07

Teresa

nr is just showing off. The INTO is optional. (he's probably overly excited by the cricket)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 09/17/2002 02:59:11

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-17 : 07:04:20

quote:

It's funny you mention that, Joe Celko has the following in SQL Puzzles & Answers:
quote:
The use of a constant in the SELECT clause instead of an asterisk is a good trick, but it depends on your SQL. Some optimizers will see that this is an EXISTS() predicate, which means that the system can replace the asterisk with one or more columns of its own choice. The optimizer then looks at which columns in the WHERE clause have indexes, and uses the best indexes for the job.
Some quick testing in SQL Server tends to show that it doesn't seem to make any difference in the execution plans (not in pubs or Northwind anyway) But I'm more inclined to think that SELECT * would be better if there are other tests in the WHERE clause besides the join conditions.

Relational databases shold be optimised for if exists(select * from ..)
At the moment sql server optimises select 1 the same way as select * in this situation.
select colname means that it has to resolve the column and also limits the indexes it can use.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-17 : 07:10:15

I never put an into after an insert as I'm not a very fast typist and it would move everything to the next tab stop and mess up my formatting.
Same as I don't write delete from.

Doesn't matter as long as you don't work for me.

>> DR Codd said that the only data type needed for a relational system is a BOOLEAN.

That's a 3 valued boolean (or 4 depending on your point of view) I guess.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-17 : 08:15:21

quote:

But I'm more inclined to think that SELECT * would be better if there are other tests in the WHERE clause besides the join conditions.

Reason being?

I'm with byrmol ... where not exists (select 1 ...) ...

In fact, sometimes when I am writing code that someone else will have to understand I will ... where not exists (select 47 ...) ... It gives cause to pause and say , "What the hell is that?". I find most non-database-thinking-people take a few seconds to wrap their minds around the EXISTS things ...

Jay White
{0}

TJ
Posting Yak Master

201 Posts

Posted - 2002-09-17 : 12:26:04

Neither of the examples did what I needed them to do. Perhaps I was too vague. I'll try to explain it as well as I can. If you need more info, please tell me.

Here are the tables with example codes:


DLRCODES TABLE

CUSTNUMBER  DLRCODE	PASSWORD  MAILBOX  ASSIGNED

<null>	     abc	sqlab	  12345	   N
<null>	     def	vbrul	  12346	   N
<null>	     ghi	prg23	  12347	   N

EFILE TABLE

CUSTNUMBER  DLRCODE  PASSWORD	MAILBOX
0123456	    <NULL>   <NULL>	<NULL>
0123457	    <NULL>   <NULL>	<NULL>
0123458	    <NULL>   <NULL>	<NULL>

I need to update the dlrcodes table with the custnumber from the efile table where the custnumber does not exist. I then need to update the efile table with the dlrcode, mailbox and password from the dlrcodes table and then update the dlrcodes.assigned column to 'Y'.

I thought the best way to do this, since there are no corresponding columns in either table at this point, was to update the dlrcode table first, bring back the corresponding data, ie: mailbox, etc to the efile table and then update the assigned field.

If my thought process needs revised in order to accomplish what needs done, please let me know.

Thanks again!
Teresa

TJ
Posting Yak Master

201 Posts

Posted - 2002-09-17 : 12:27:55

quote:

nr is just showing off. The INTO is optional. (he's probably overly excited by the cricket)

Someday, when I become an adept SQL programmer, I might show off too!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-17 : 13:12:00

>> If my thought process needs revised in order to accomplish what needs done, please let me know.

Think that could be it.
How do you know which entry in DLRCODES needs to be updated with which CUSTNUMBER from EFILE?

If it is the first free records from each because you need to order by soomething.

If it doesn't matter which then something like

set rowcount 1
select 1
while @@rowcount > 0
begin
update DLRCODES
set CUSTNUMBER = (select top 1 EFILE.CUSTNUMBER
from EFILE
where not exists (select * from DLRCODES where EFILE.CUSTNUMBER = DLRCODES.CUSTNUMBER))
where DLRCODES.CUSTNUMBER is null
and DLRCODES.ASSIGNED = 'N'
end

set rowcount 0

update EFILE
set DLRCODE = DLRCODES.DLRCODE ,
...
from DLRCODE
where EFILE.CUSTNUMBER = DLRCODES.CUSTNUMBER
and DLRCODES.ASSIGNED = 'N'

update DLRCODE
set ASSIGNED = 'Y'
where ASSIGNED = 'N'
and CUSTNUMBER is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources