Hi, sorry, that was sent before i'd finished.
Right, I was going to give you an example.
A table is imported and appended onto another table (called Raw). This
contains lab results which are sometimes duplicates i.e sometimes an
experiment is repeated for some reason (maybe the first result was a
failure). A duplicate will have 'category,Individual_ID' values that are the
same for each row.
i.e
category | Individual_ID
row1 45 | 57
row2 45 | 57
Firstly, the query below is run.
create temporary table tmp select Raw.category,Raw.Individual_ID,
Raw.Inactivate from
Raw where individual_id not like "control%" and inactivate=0
group by category,Individual_ID, Inactivate having count(*)>1
This finds the duplicates from the main table (Raw) and puts them into a
temporary table 'tmp'. It shows which rows are duplicates but does not show
each of the duplicates. The following does this by creating a table called
Unresolved_duplicates.
create temporary table Unresolved_duplicates SELECT DISTINCTROW
Raw.Category, Raw.Ind
ividual_ID, Raw.Inactivate, Raw.Key_num, Raw.Peak1, Raw.Peak2,
Raw.Peak1_Real, Raw.Peak2_Real, Raw.Filename
FROM Raw,tmp
WHERE Raw.category=tmp.category and Raw.Individual_ID=tmp.Individual_ID and
Raw.Inact
ivate=0
ORDER BY Raw.Category, Raw.Individual_ID, Raw.Inactivate
The following query then creates a summary table called
Unresolved_duplicates_perm which contains the unique id(key) of each of the
duplicates
create table Unresolved_duplicates_perm SELECT
Unresolved_duplicates.Category, Unreso
lved_duplicates.Individual_ID, Min(Unresolved_duplicates.Key) AS MinOfKey,
Max(Unreso
lved_duplicates.Key) AS MaxOfKey, Min(Unresolved_duplicates.Peak1) AS
MinOfPeak1
FROM Unresolved_duplicates
GROUP BY Unresolved_duplicates.Category, Unresolved_duplicates.Individual_ID
Finally, I want to deactivate the row which has the lower unique id(column
'key') if it has a peak value of zero
update Raw,Unresolved_Duplicates_perm set Raw.Inactivate = 1 where
Raw.Key_num = Unresolved_Duplicates_perm.MinOfKey
and Raw.Peak1=0;
It is with this last query that there is a problem. I can do it in Access
with an INNER JOIN.
Hope that's clear :-)
thanks
Rich
>From: "DL Neil" <[EMAIL PROTECTED]>
>Reply-To: "DL Neil" <[EMAIL PROTECTED]>
>To: "Richard Dobson" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
>Subject: Re: update from table x to table y
>Date: Tue, 13 Nov 2001 21:13:19 -0000
>
>=No there isn't, because:
>
>7.24 UPDATE Syntax
>UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
> SET col_name1=expr1, [col_name2=expr2, ...]
> [WHERE where_definition]
> [ORDER BY ...]
> [LIMIT #]
>
>- there is only 'space' for one tbl_name in the UPDATE clause.
>
>=I've had a quick look around. I'm sure someone on the list mentioned this
>recently (but I can't find the
>msg/must have deleted it). This sort of functionality might not be very far
>away in the MySQL development
>plan...
>
>=coming back to your original question: can this dual-table update actually
>be done in Access?
>
>=coming back to my recent point, is the data properly normalised? I'm
>having difficulty (don't I always?) in
>visualising a reason for doing this. Would you like to discuss your
>application and put some sample data in
>front of us, in the hope that someone might be able to make a sensible (and
>successful) suggestion? Also what
>tool(s) are you using to access MySQL?
>
>=Sorry!
>=dn
>
>
> > Hi, thanks dn,
> > so, to confirm, there is no MySQL to represent the following?:
> >
> > update Raw,Unresolved_Duplicates_perm set Raw.Inactivate = 1 where
> > Raw.Key_num = Unresolved_Duplicates_perm.MinOfKey
> > and Raw.Peak1=0
> >
> > Thanks
> > Rich
> >
> > >From: "DL Neil" <[EMAIL PROTECTED]>
> > >Reply-To: "DL Neil" <[EMAIL PROTECTED]>
> > >To: "Richard Dobson" <[EMAIL PROTECTED]>,
><[EMAIL PROTECTED]>
> > >Subject: Re: update from table x to table y
> > >Date: Tue, 13 Nov 2001 13:49:23 -0000
> > >
> > >Duplicating values (cf keys) in tables involves de-normalisation and is
> > >therefore not recommended.
> > >
> > >Your observation row cf column is correct. I felt the question was
> > >sufficiently broad to risk interpreting
> > >"update" as wider than "UPDATE", sorry - the other response seemed to
>hit
> > >that nail on the head (have deleted
> > >it).
> > >
> > >If it doesn't suit, then another solution might be to use PHP to SELECT
>the
> > >data from table1 and then use a
> > >second query to UPDATE table-2 SET table2-col = table1-col WHERE
>table1-val
> > >= table2-val (which, in its present
> > >form, has the potential to modify numerous rows in one hit).
> > >
> > >=Regards,
> > >=dn
> > >
> > >
> > > > thanks for that, but that will add a row as opposed to updating a
>column
> > > > won't it?
> > > > I don't want to insert a new row.
> > > > All I want to do is update a value in table1 if it is present in
>table2
> > > >
> > > > cheers
> > > > Rich
> > > >
> > > > >From: "DL Neil" <[EMAIL PROTECTED]>
> > > > >Reply-To: "DL Neil" <[EMAIL PROTECTED]>
> > > > >To: "Richard Dobson" <[EMAIL PROTECTED]>,
> > ><[EMAIL PROTECTED]>
> > > > >Subject: Re: update from table x to table y
> > > > >Date: Tue, 13 Nov 2001 11:45:45 -0000
> > > > >
> > > > > > Hi does anyone know of a way of taking some data from one table
>and
> > > > >updating
> > > > > > another table with it?
> > > > > >
> > > > > > If MySQL doesn't support it i'm gonna have to go back to Access
>or
> > > > > > something!
> > > > >
> > > > >
> > > > >Rich
> > > > >
> > > > >=only "go back" to Access if you're a glutton for punishment!
> > > > >
> > > > >=try MySQL/HTMLmanual/manual_Reference.html#INSERT_SELECT
> > > > >
> > > > >=dn
> > > > >
> > > > >
> > > > >
> > > >
> >---------------------------------------------------------------------
> > > > >Before posting, please check:
> > > > > http://www.mysql.com/manual.php (the manual)
> > > > > http://lists.mysql.com/ (the list archive)
> > > > >
> > > > >To request this thread, e-mail <[EMAIL PROTECTED]>
> > > > >To unsubscribe, e-mail
> > > > ><[EMAIL PROTECTED]>
> > > > >Trouble unsubscribing? Try:
>http://lists.mysql.com/php/unsubscribe.php
> > > > >
> > > >
> > > >
> > > > _________________________________________________________________
> > > > Get your FREE download of MSN Explorer at
> > >http://explorer.msn.com/intl.asp
> > > >
> > > >
> > >
> > >
> > >---------------------------------------------------------------------
> > >Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > >To request this thread, e-mail <[EMAIL PROTECTED]>
> > >To unsubscribe, e-mail
> > ><[EMAIL PROTECTED]>
> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
