data_tmp

lab_sample_id,conductivity_pS_cm,DO_mg_per_l,pH
VM_WR_09_0100_0,76.5,4.15,5.54
VM_WR_09_0100_1,76.6,4.02,5.56

removed_data

lab_sample_id,value
VM_WR_09_0100_0,76.5
VM_WR_09_0100_0,5.54
VM_WR_09_0100_1,4.02

The statement below lets me accomplish the removal of "removed data"
for one column in the data_tmp table but I need this to occur for
every column in data_tmp. I'm doing this through a script so I was
hoping to do it with one query. I've had some very good ideas come
out of this group before so I thought I would give it another shot.
Any suggestions would be very much appreciated.

UPDATE data_tmp as t1
LEFT JOIN removed_data AS t2 ON t1.lab_sample_id = t2.lab_sample_id
SET t1.conductivity_pS_cm = NULL
WHERE t1.conductivity_pS_cm = t2.value

On 07-10-2011 15:25, qtrimble wrote:
create table set1 (i int, j int);
create table set2 (i int, j int);
INSERT into set1 values (1,1);
INSERT into set1 values (1,2);
INSERT into set1 values (1,3);
INSERT into set2 select i,j, from set1;

select * from set1 where (i,j)=(select i,j from set2 where set1.i=set2.i
and set1.j=set2.j)

This construction in the WHERE-clause can also be used in an update
statment...


--
Luuk

I'll have a unique WHERE clause for each SET so something like this...

UPDATE wr_table_tmp as t1
LEFT JOIN removed_fields_wr AS t2 ON t1.id = t2.id
SET t1.conductivity_pS_cm = NULL
WHERE t1.conductivity_pS_cm = t2.value;
UPDATE wr_table_tmp as t1
LEFT JOIN removed_fields_wr AS t2 ON t1.id = t2.id
SET t1.DO_mg_per_l = NULL
WHERE t1.DO_mg_per_l = t2.value;

This actually works but it is not very elegant.

On 10/7/2011 4:25 PM, qtrimble wrote:
That's what you need to do, though.

But what's not elegant about it? You are updating different columns
based on different conditions.

Even if there were another way to do it, I would much prefer performing
them as separate operations. Otherwise the code will quickly become
very convoluted and hard to understand.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-10-07 22:25, qtrimble wrote:
I'm not sure I understand your datamodel. How do you determine whether a
t2.value refers to conductivity, pH or something else? I assume the
domain for these attributes are not disjoint, correct?

That said, a construction like:

update wr_table_tmp as t1
left join removed_fields_wr as t2
ON t1.id = t2.id
SET t1.conductivity_pS_cm = nullif(t1.conductivity_pS_cm, t2.value)
, t1.DO_mg_per_l = nullif(t1.DO_mg_per_l, t2.value)
, t1.pH = nullif(t1.pH, t2.value)
where t2.value in ( t1.conductivity_pS_cm, t1.DO_mg_per_l, t1.pH );

might work. However, update of a left join is not supported in iso/ansi
standard and the semantics of this construct is unclear to me. I have
not tried it, but given your sample data two different t1 rows in the
table that is constructed from the join is updated (conductivity_pS_cm =
76.5 -> null and ph = 5.54 -> null). How this intermediate result should
be merged back to wr_table_tmp is unknown to me. It's easy enough to
construct a conflict, where one intermediate row has to be chosen over
the other.

To conclude, I would stick with your original approach. FWIW, the left
join can be replaced with an exists predicate.



/Lennart

On 2011-10-08 09:26, Lennart Jonsson wrote:
[...]
I did not find any official info regarding this, but a post by James
Goatcher on March 16 2007 9:28pm, see

http://dev.mysql.com/doc/refman/5.5/en/update.html

indicates that a random row is picked, (actually the first found, but
since there is no defined order I consider it random).

[...]