The multi-table UPDATE syntax in MySQL is different from Microsoft SQL Server. You don’t need to say which table(s) you’re updating, that’s implicit in your SET clause. UPDATE tableA a JOIN tableB b ON a.a_id = b.a_id JOIN tableC c ON b.b_id = c.b_id SET b.val = a.val+c.val WHERE a.val > 10 AND c.val > … Read more
How about this: UPDATE p SET p.extrasPrice = t.sumPrice FROM BookingPitches AS p INNER JOIN ( SELECT PitchID, SUM(Price) sumPrice FROM BookingPitchExtras WHERE [required] = 1 GROUP BY PitchID ) t ON t.PitchID = p.ID WHERE p.bookingID = 1
I have to update tables of 1 or 2 billion rows with various values for each rows. Each run makes ~100 millions changes (10%). My first try was to group them in transaction of 300K updates directly on a specific partition as Postgresql not always optimize prepared queries if you use partitions. Transactions of bunch … Read more
WITH q AS ( SELECT TOP 10 * FROM messages WHERE status = 0 ORDER BY priority DESC ) UPDATE q SET status = 10
select v.SQL_TEXT, v.PARSING_SCHEMA_NAME, v.FIRST_LOAD_TIME, v.DISK_READS, v.ROWS_PROCESSED, v.ELAPSED_TIME, v.service from v$sql v where to_date(v.FIRST_LOAD_TIME,’YYYY-MM-DD hh24:mi:ss’)>ADD_MONTHS(trunc(sysdate,’MM’),-2) where clause is optional. You can sort the results according to FIRST_LOAD_TIME and find the records up to 2 months ago.
Try ;with counts AS ( SELECT total, COUNT(*) as dos FROM temp_table2010 WHERE total in (select id from #temp) ) UPDATE T SET dos=counts.dos FROM #temp T INNER JOIN counts ON t.id = counts.total
Drop the SELECT statement, there is no need for, just use the current value: UPDATE some_table SET column_1 = COALESCE(param_1, column_1), column_2 = COALESCE(param_2, column_2), column_3 = COALESCE(param_3, column_3), column_4 = COALESCE(param_4, column_4), column_5 = COALESCE(param_5, column_5) WHERE id = some_id;
You want chr(10) instead of char(10). Be careful with this, because that might be the wrong newline. The “right” newline depends on the client that consumes it. Macs, Windows, and Linux all use different newlines. A browser will expect <br />. It might be safest to write your update using an escape string for PostgreSQL … Read more
MySQL supports IF statement. UPDATE abbonamento SET punti = IF(tipo = ‘punti’, punti – 1, punti), bonus = IF(tipo <> ‘punti’, bonus – 1, bonus) WHERE id = 17 or you can also use CASE UPDATE abbonamento SET punti = CASE WHEN tipo = ‘punti’ THEN punti – 1 ELSE punti END, bonus = CASE … Read more
The easiest solution in your case is to use ON DUPLICATE KEY UPDATE construction. It works really fast, and does the job in easy way. INSERT into `table` (id, fruit) VALUES (1, ‘apple’), (2, ‘orange’), (3, ‘peach’) ON DUPLICATE KEY UPDATE fruit = VALUES(fruit); or to use CASE construction UPDATE table SET column2 = (CASE … Read more