Home » SQL & PL/SQL » SQL & PL/SQL » how to get the Max
how to get the Max [message #186102] Sat, 05 August 2006 19:44 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
I want to update ALL the date field for every id where max number = 0.
So, In my case the records with the Id's 2345 and 3456 should be updated.
actually...the query is on my_table2, but the update is done in another table, which has the same table definition.

table My_table2

id name addres number date

1234 me home 0 2001
1234 me home 1 2002
1234 me home 2 2002
2345 he hotel 0 2003
3456 she motel 0 2003

something like:

update My_table1 mt1
set mt1.date = mt2.date
where mt2.id = mt1.id
,AND mt2.name = mt1.name
,AND mt2.address = mt1.address


but I want this only for all the records where the MAX(number) = 0 in my_table2
Can anyone help me out how to fit this in the query above?

thanks

[Updated on: Sat, 05 August 2006 19:46]

Report message to a moderator

Re: how to get the Max [message #186140 is a reply to message #186102] Sun, 06 August 2006 07:16 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
update My_table1 mt1
set mt1.date = mt2.date
where mt2.id = mt1.id
,AND mt2.name = mt1.name
,AND mt2.address = mt1.address

,AND mt2.MAX(number) = 0

Check the results before running on this a production database.

Re: how to get the Max [message #186157 is a reply to message #186140] Sun, 06 August 2006 11:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This query can easily be run on production database, as it won't work. Where did you get "mt2" values from? You miss a subquery, don't you think?
Re: how to get the Max [message #186161 is a reply to message #186102] Sun, 06 August 2006 11:17 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi Littlefoot,

Yes, You are very very right about that I can run this on a production database. I guess that there is a little misunderstanding.

what I actually want is this :

table My_table2

id name addres number date

1234 me home 0 2001
1234 me home 1 2002
1234 me home 2 2002
2345 he hotel 0 2002
3456 she motel 0 2002

table My_table

id name addres number date

2345 he hotel 0 2003
3456 she motel 0 2003

I want to select all the records with a max number = 0 and update the date fields with the date fields of the other table.
(in this case only the records with id = 2345 and 3456 have a max id = 0)
I need to update all the date fields from my_table2 with the date fields of my_table, but only for the records which have a MAX number = 0.

can you help you out? or anyone else....

thanksss
Re: how to get the Max [message #186162 is a reply to message #186161] Sun, 06 August 2006 11:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you well, perhaps something like this might work:
UPDATE MY_TABLE1 m1 SET
  m1.date = (SELECT m2.date FROM MY_TABLE2 m2
             WHERE m2.id = m1.id
               AND m2.name = m1.name
               AND m2.address = m1.address
               AND m2.id IN (SELECT m.id FROM MY_TABLE2 m
                             GROUP BY m.id
                             HAVING MAX(m.number) = 0
                            )			   
            )
WHERE EXISTS (SELECT NULL FROM MY_TABLE2 m2
              WHERE m2.id = m1.id
              GROUP BY NULL
              HAVING MAX(m2.number) = 0
             );

By the way, I hope your column names aren't really YEAR and NUMBER.
Re: how to get the Max [message #186191 is a reply to message #186162] Sun, 06 August 2006 21:13 Go to previous message
Jolanda
Messages: 68
Registered: September 2004
Member
Sorry You all,

I should be more detailed.

And Littlefoot, Ofcourse these were not the real column names Smile))

the problem is this :


I Need an update statement which updates my_table2 with record values from x_my_table, but only for the records which have a max NUMBER=0
So, The records with ID = 1 and NAME = ME have a Max Number of 1, so these can be ignored.
However, the records with ID = 2 and Name = HE and ID = 3 and name = She do have a max number = 0

my_table2 should be updated with these 2 records only.
with the values :

my_table2.LOGIC_VAL = x_my_table.LOGIC_ONT and
my_table2.TECH_VAL = x_my_table.TECH_ONSTAAN

and the processed records in x_my_table must be updated to P


x_my_table
ID NAME LOGIC_VAL LOGIC_ONT TECH_VAL TECH_ONSTAAN NUMBER IND
1 me 0 J
1 me 1 J
2 he 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 J
3 she 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 J

my_table2
ID NAME LOGIC_VAL LOGIC_ONT TECH_VAL TECH_ONSTAAN NUMBER IND
1 me 0 J
1 1 J
2 he 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 J
3 she 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 J

should give this in my_table2

ID NAME LOGIC_VAL LOGIC_ONT TECH_VAL TECH_ONSTAAN NUMBER IND
1 me 0 J
1 me 1 J
2 he 1-2-2006 1-2-2006 1-4-2006 1-4-2006 0 J
3 she 1-2-2006 1-2-2006 1-4-2006 1-4-2006 0 J

only the values of LOGIC_VAL and TECH_VAL change

and x_my_table

x_my_table
ID NAME LOGIC_VAL LOGIC_ONT TECH_VAL TECH_ONSTAAN NUMBER IND
1 me 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 J
1 me 1-1-2006 1-2-2006 1-3-2006 1-4-2006 1 J
2 he 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 P
3 she 1-1-2006 1-2-2006 1-3-2006 1-4-2006 0 P

Can someone pleaseeeee help me on this one. I am working on this for hours and hours, but unfortenately I haven't been very succesful Sad

Thanks

[Updated on: Sun, 06 August 2006 21:36]

Report message to a moderator

Previous Topic: very complex group by challange
Next Topic: Package Problems
Goto Forum:
  


Current Time: Thu Dec 08 14:32:28 CST 2016

Total time taken to generate the page: 0.10881 seconds