Update SQL

[noob999]

I have an issue with my update SQL.

Table A is existing EMPLOYEES table with 10000+ records.
Table B is a EMPTEMP table where I have loaded all the information for the employees I need to update (only about 1161 records).

My SQL Update statement:

Update employees em SET (lname, startdate, enddate, emercontact) = (select lname, startdate, enddate, emergency_contact from emptemp et where em.emplid = et.employeeid)

That sql will update all 10000+ records and make all other records and fields NULL besides the 1161 records.

My question is what am I doing wrong?

thank you

noob

 

[LKBrwnDBA]

Try:

Update employees em 
   SET (lname, startdate, enddate, emercontact) 
     = (select lname, startdate, enddate, emergency_contact
          from emptemp et where em.emplid = et.employeeid) 
WHERE EXISTS
       (select 'Yes'
          from emptemp et where em.emplid = et.employeeid)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mrdenny]

What platform are you working in?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[noob999]

Thank you, LKBrwnDBA; I will try that.

mrdenny, I'm working in Oracle 10g.

 

[PHV]

Another way:
Update employees em
SET (lname, startdate, enddate, emercontact)
= (select lname, startdate, enddate, emergency_contact
from emptemp et where em.emplid = et.employeeid)
WHERE emplid IN (select employeeid from emptemp)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noob999]

it doesn't work.

It's updated 2000+ records instead of the 1161. Any ideas why all this is happening?

thks!

 

[kjv1611]

noob999,

WHAT doesn't work? You've had 2 suggestions so far.

And HOW does it not work? Any messages? What results are you getting? What can you tell us?

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Did you try my suggestion ?

 

[Trevoke]

You'll be rolling back, won't you, so you can undo these changes you made.. Or is that not important ?

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[noob999]

Yes. I have rollback. Both did not work ... for some reasons it updated 1355 records and not just the 1161 that I wanted to. There were no errors or messages. The only problem is the number of records it updated.

 

[PHV]

Do you have duplicates emplid or employeeid ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noob999]

No. I have checked for duplicates.

 

[PHV]

What are the results of the following SQL ?
SELECT COUNT(*) FROM emptemp;
SELECT COUNT(DISTINCT employeeid) FROM emptemp;
SELECT COUNT(*) FROM employees WHERE emplid IN (SELECT employeeid FROM emptemp):
SELECT COUNT(DISTINCT emplid) FROM employees WHERE emplid IN (SELECT employeeid FROM emptemp):

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noob999]

sorry for the late reply; was out of office the remaining of last week.
I will try those and post the results later today.

Thanks for all your help.

 

[noob999]

SELECT COUNT(*) FROM emptemp;

1161 records

SELECT COUNT(DISTINCT employeeid) FROM emptemp;

1161 records

SELECT COUNT(*) FROM employees WHERE emplid IN (SELECT employeeid FROM emptemp):

1161

SELECT COUNT(DISTINCT emplid) FROM employees WHERE emplid IN (SELECT employeeid FROM emptemp):

1161

 

[PHV]

So, I'd try this:

UPDATE employees 
   SET (lname, startdate, enddate, emercontact) 
     = (SELECT lname, startdate, enddate, emergency_contact
          FROM emptemp WHERE employeeid=employees.emplid) 
 WHERE emplid IN (SELECT employeeid FROM emptemp)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886