Updatig records from / to same table

[pingLeeQuan]

Hello, I have a table that contains records with duplicate employee numbers. Each employee has MANY records. Some of those records are updated with project id and some are not (for the same employee). I would like to run a query to update the old records for the same employee with information from the new record that contains the project id information.

I looked into SELECT ... UPDATE but it does not cover the same table, multiple records.

I could do it using C++ but i thought I should be able to do it in MYSQL using SQL statements.

Any thought on this subject.

thanks again for all of your help
--

 

[sleipnir214]

So if an employee has many records, you want to update the project id field for all of that employee's records?

If so, a simple UPDATE query will do what you want to do.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[pingLeeQuan]

Thanks sleipnir214 for your respond.... well... The employee has 10 records and 1 to 3 records have the project id in them but not the rest.

I wante to know if there is a way to actually update the rest of the records (in the table) without having a circular reference errors.

I even copied all the records that have a project id data into another table called summary.

The following is something i started but i could not take further...

SELECT projectid, lname, fname FROM tSummary FOR UPDATE;
UPDATE tExternalData
SET
tExternalData.projectid = tSummary.projectid,
tExternalData.lname = tSummary.lname,
tExternalData.tenant_fname = tSummary.tenant_fname
where tExternalData.ssnumber = (select ssnumber from tSummary where ssnumber = ?)

thanks again for any help

 

[sleipnir214]

Again, subqueries like the one you're trying to use:

where tExternalData.ssnumber = (select ssnumber from tSummary where ssnumber = ?)

aren't supported in MySQL until version 4.1. Version 4.1 is defined as alpha-release software and should not be used in a production environment.

The problem is, which of the existing project ID values are you going to use to update the empty records?

I think this is a case where you should use an external programming language to update the table.

Want the best answers? Ask the best questions!

TANSTAAFL!!