SELECT and UPDATE problem...

[SFiend]

Here's the query:

SELECT Zbozi1.*
FROM Zbozi AS Zbozi1, Zbozi AS Zbozi2
WHERE Zbozi1.Id_polozky LIKE Zbozi2.Id_polozky AND Zbozi1.Poznamka LIKE Zbozi2.Poznamka AND Zbozi2.Id LIKE $Id

But, what about I want update the result?

This doesn't work

UPDATE Zbozi AS Zbozi1, Zbozi AS Zbozi2
SET Zbozi1.Datum_prodeje=$Datum_prodeje, Zbozi1.Id_prodavajiciho=$Id_prodavajiciho
WHERE Zbozi1.Id_polozky LIKE Zbozi2.Id_polozky AND Zbozi1.Poznamka LIKE Zbozi2.Poznamka AND Zbozi2.Id LIKE $Id

 

[sleipnir214]

I don't understand. In your select, you're performing an inner join on the same table twice. Won't your SELECT query collapse to:

SELECT *
FROM Zbozi
WHERE Id LIKE $Id


?

If so, doesn't that similarly simplify your update query?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[SFiend]

I know only one Id.

So in the first step I have to find out all Ids, which have the same Id_polozka and Poznamka as Id I know.

And then I can update them.

I hope you understand me now.

 

[sleipnir214]

It sounds to me like you're looking for all rows in the table where the column "id" is the same as your id value, or the column "id_polozka" is the same as your id value, or the column "Poznamka" is the same as your id value. If so, you still don't need an inner join of a single table to itself.

The SELECT query should probably be:

SELECT *
FROM Zbozi
WHERE Id = $id or id_polozka = $id or Poznamka = $id

And your UPDATE query will use a similar WHERE clause.




Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[SFiend]

No, I'll try to explain it a little bit better...

I have a table:

Id Id_item Note Date
1  1       Good 03/03/04
2  1       Good 05/06/04
3  2            02/03/04
4  1            08/04/03
5  3            07/08/03
6  2       Bad  04/02/03
7  2       Bad  02/01/03

Now I do this:

select *, count(*) as Number_of from table group by Id_item, Note;

The result'll be something like this:

Id Id_item Note Date     Number_of
1  1       Good 03/03/04 2
3  2            02/03/04 1
4  1            08/04/03 1
5  3            07/08/03 1
6  2       Bad  04/08/03 2

--------------------------------

Now let's talk about the first line. The number_of is 2. Now I know the Id of the first row in the group. But how can I find out the Id of the rest?

Here is my solution:

select Id from table as table1, table as table2 where table1.Id_item=table2.Id_item and table1.Note=table2.Note and table2.Id=[i]Id[/i];

So, it works. But I'm not able to update this table by similar query with "update".

update table as table1, table as table2 set table1.Date=NOW() where table1.Id_item=table2.Id_item and table1.Note=table2.Note and table2.Id=[i]Id[/i];

 

[SFiend]

So, it works as well, I'm still trying use ORDER BY in multiple-table UPDATE.

But when I need to update items in some ORDER? What about now?

 

[SFiend]

Or only the first 5 items? I don't need update all this items

 

[sleipnir214]

This from the MySQL online manual entry on the UPDATE query:

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Do you have a programming-language frontend? It might be easier to do this that way.



Want the best answers? Ask the best questions!

TANSTAAFL!!