update sa dugackim where ... nesto ne valja.

[ Branko Santo @ 08.03.2006. 23:17 ] @

Code:
update rezultatitiketa set tiketwon = 1 where tiketID = 46, count(tiketID) = count(won), min(won) = 1;

Imam ovakvu SQL komandu koju unosim u MySQL query browser i on mi prijavljuje da imam eror u sintaksi.

Hteo bih da podesim kolonu tiketwon = 1 na redovima kojima je tiketID = 46 kao i count(tiketID) = count(won) kao i min(won) = 1 (broj utakmica na tiketu jednak zavrsenim utakmicama na tiketu (kolone tiketID i won) kao i da su sve utakmice pogodjene (min(won) = 1))

Ima li pomoci?

Ah da probao sam i da zareze u where zamenim sa "AND" ali mi onda prijavi pogresno koriscenje group funkcije.

_{[Ovu poruku je menjao long_strider dana 09.03.2006. u 00:18 GMT+1]}

[ noviKorisnik @ 09.03.2006. 00:03 ] @

... kako izgleda tabela rezultatitiketa?

[ Branko Santo @ 09.03.2006. 07:33 ] @

[ Ana Komazec @ 10.03.2006. 00:10 ] @

Problem je sto agregatne funkcije ne mogu stajati u klauzuli Where

Probaj ovako da uradish, mada nisam sigurna da li radi:

Code:

Update RezultatiTiketa
Set tiketwon=1
Where tiketID=1
          and won in (Select min(won)
                           From RezultatiTiketa
                            Having (min(won)=1 and Count(tiketID)=Count(Won)));

Pozdrav

_{[Ovu poruku je menjao Ana Komazec dana 10.03.2006. u 01:10 GMT+1]}

[ Branko Santo @ 10.03.2006. 08:35 ] @

Probacu! :)

Hvala! :)

[ Branko Santo @ 10.03.2006. 09:34 ] @

Ana hvala mnogo, ne radi jos ali mislim da cu uspeti da namestim.

Ne moze da se radi select u subquery tabele koja se updatuje ili kako bi rekli u knjizi

Citat:

Although subqueries can be used to retrieve or aggregate data from other tables for use in statements that modify tables (such as UPDATE, DELETE, INSERT, and REPLACE), MySQL does not allow a table that is being updated in the outer query to be selected from in any subquery of the statement.

:)
Hvala na usmerenju!

[ Ana Komazec @ 10.03.2006. 13:02 ] @

Evo jos nekih informacija koje ti mozda mogu pomoci:

Citat:

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

[url]http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html[/url]

A sto se tice pogleda (view):

Citat:

Subqueries cannot be used in the FROM clause of a view. This limitation will be lifted in the future.

[url]http://dev.mysql.com/doc/refman/5.1/en/view-restrictions.html[/url]

Pozdrav

[ Branko Santo @ 10.03.2006. 21:23 ] @

Ana imas pice kad dodjes u NS :)

Uspeo sam nekako da napravim

Code:

update tiketi set tiketwon = -1 where
(select count(tiketID) from utakmiceodigrane where tiketID = 46) =
(select count(won) from utakmiceodigrane where tiketID = 46)
and (select min(won) from utakmiceodigrane where tiketID = 46) = -1
and tiket = 46 and tiketwon = null;

Malo je mozda rogobatno ali sta cu kad sam jos mali :)

Hvala bas sam se sad malo izvezbao sa ovim subquery-jima :P