New answers tagged

1

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though). If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using ...


0

Few Quick Possibilities: Bad Statistics. Seems unlikely on a table under millions of rows, but technically possible. Try running UPDATE STATISTICS . If this does work look into why your statistics are getting out of whack and what you can do to mitigate the problem. Lack of a good Clustered Index. This can be related to #1, if your CI is bad it can slow ...


0

It doesn't look like an SQL issue based on what you've posted. It's possible the web service has a bug in its status handling. Even if the web service was swallowing errors, SQL doesn't allow only one column of two to be updated in the same statement; and triggers have an implicit transaction plus your update there is a single statement. As you're on ...


0

Don't update rank in the main table. Instead, put it into a separate table that you rebuild every night. (You can use the inner part of Jehad's answer for building the table.) Furthermore, do it this way to avoid any 'downtime': CREATE TABLE new ...; INSERT INTO new ...; RENAME TABLE Ranks TO old, new TO Ranks; DROP TABLE old; Once you have perfected ...


4

The statement looked perfectly fine in the first place. After excluding other options (like tables referenced without schema), it seems like the problem is caused by the 4 UPDATE triggers on the table (or one of them). You should inspect the trigger code and understand what they are doing and which one of them is causing this behaviour. After that, it's ...


1

This solution requires first that there aren't any conflicting or cascading information in plant_name_mapping; to work, the following query must return no records: select * from plant_name_mapping o join plant_name_mapping n ON ((n.newgenus, n.newspecies) = (o.oldgenus, o.oldspecies)); The queries to update your tables are: create temp table ...


2

Try the following: UPDATE tests AS t JOIN testusers AS tu ON t.TestNumber= tu.TestNumber SET t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot)) WHERE tu.UserId=25 AND tu.UserSlot >= 0;


1

MySQL doesn't have syntax for UPDATE ... FROM, however it does allow for UPDATE table1, table2,... SET table1.col = value WHERE table1.id = table2.id. You can try the following: UPDATE Tests AS t, (SELECT TestNumber, UserSlot FROM TestUsers WHERE UserId=25 AND UserSlot >= 0) AS tu SET t.InactiveTestSlotBitwise = ...


2

You're almost there: Add order by Score1, score2, and enclose it with an update statement: update players, (SELECT UserId, Name, Score1, score2, Rank FROM (SELECT userid, Name, Score1, Score2, @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank, @incRank := ...


4

In MySQL, you can use tuple comparison: WHERE (TestId, TestSubId) IN ((10,25), (11,22)) That looks nice and succinct, although, as ypercubeᵀᴹ mentioned in a comment, it may not work well performance-wise. However, given how the conditions are re-used in your UPDATE statement, you could also take a different approach altogether: represent the affected IDs ...


2

You cannot use the in operator to filter two different columns. For this example, you would have to use the standard logical operators: UPDATE Tests SET TestScore = CASE WHEN (TestId = 10 AND TestSubId = 25) THEN 1000 WHEN (TestId = 11 AND TestSubId = 22) THEN 1100 END, TestScore2 = CASE WHEN (TestId = 10 AND TestSubId = 25) THEN 2000 WHEN ...


3

Here is another approach, which, however, is likely to perform worse than the other suggestion: UPDATE Beleg SET SortID = ( SELECT COUNT(*) FROM Beleg AS Beleg2 WHERE Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta ) AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto AND Beleg2.SortID <= Beleg.SortID ) WHERE ...


2

If you're able to create a table, and update allows you to use the result of a subselect (all valid SQL92 code), you should be able do perform the following multi-step process: CREATE TABLE sorting (sid numeric(10,10), rn int); INSERT INTO sorting (sid, rn) SELECT SortID, RecordNumber FROM Beleg WHERE Year ( Valuta ) = 2016 AND Ursprungskonto = 1210 ORDER ...


0

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )


1

I think that you want something like the following: BEFORE: ('' is not a valid date - used null instead) mysql> select * from temp_table; +------------+------------+---------------------+---------------+--------------------+------------------+ | invoice_id | payment_id | payment_due_date | period_months | service_start_date | service_end_date | ...


0

thanks for the help. worked with the following ... UPDATE table1 SET value = t2.rename FROM table2 t2 WHERE t2.rename IS NOT NULL AND id = t2.id;


4

Use a FROM clause to join to more tables: UPDATE table_1 t1 -- alias optional SET column_x = t2.rename FROM table_2 t2 WHERE t2.rename IS NOT NULL -- assuming you actually meant t1 AND t2.id = t1.id; This joins rows from table_2 in a CROSS JOIN - filtered by the condition t2.id = t1.id which makes it an INNER JOIN effectively. This way, rows ...


1

It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.


1

Try using an INNER JOIN statement: UPDATE rooms r INNER JOIN reservation s ON s.room_no = r.room_no SET r.vacant=true WHERE s.cus_id=1



Top 50 recent answers are included