SQL – Update Table for Unique ID with Multiple Entries

update

I'm trying to update a table to fill in a variable to each row in the table if one row for the unique ID shows that variable.

I was trying to do the following, but it's giving me an error. Appreciate the help!

update patient_encounter 
Set user_defined4 = 'DTC'
where person_id = (SELECT person_id from patient_encounter where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

I want to fill in "DTC" into the user_defined4 field for any person_id that shows the particular mrkt_plan_id listed (which correlates to DTC) for any of their entries. Each person_id has multiple encounters and I want them all to share the same market plan once it is listed as DTC once.

The error says Subquery returned more than 1 value.
Thanks!

Best Answer

There multiple entry in table patient_encounter for a person_id, so you need to get the person_id with top 1

Try this

update patient_encounter 
Set user_defined4 = 'DTC'
where person_id = (SELECT top 1 person_id from patient_encounter 
                     where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

EDIT as per comment, if you are looking to update all the record, use IN as below

update patient_encounter 
    Set user_defined4 = 'DTC'
    where person_id IN (SELECT person_id from patient_encounter 
                         where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Best Answer

Related Solutions

Update field1.table1 based on compound join between table1 and table2 – Oracle

Mysql – Update one table from another table while sorting that table based on one column