Tag Info

New answers tagged

1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...


0

If your db2.table1 and db2.table2 tables are joined by some key field and the name field in both databases is unique, you can use the following query to update your data: UPDATE `db1`.`table1` t SET t.`birthday` = (SELECT t2.`birthday` FROM `db2`.`table1` t1 JOIN `db2`.`table2` t2 ON (t1.`key` = t2.`key`) WHERE t.`name` = t1.`name`) This should work if ...


0

You need to create a sub-query with distinct values and then join it to your main table and update, like this: You will need a temporary numbers table: CREATE TABLE numbers ( n INT PRIMARY KEY); INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); UPDATE users u JOIN ( SELECT ID, GROUP_CONCAT(pseudo SEPARATOR ';') pseudo ...


2

Besides using explicit transactions, I typically write a SELECT statement first to see how many rows would be affected, and then copy the WHERE clause to my UPDATE statement. SELECT * FROM [SomeTable] WHERE [SomeDateColumn] > '3/1/2014' From this I see 100 rows would be updated. So I either copy the WHERE clause, or convert the SELECT into an UPDATE ...


3

Study the following: BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real Also read XACT_ABORT @@ROWCOUNT IF ...ELSE GOTO @@TRANCOUNT More advanced topics, optional until they're not: SET TRANSACTION ISOLATION LEVEL Gail Shaw's intro to deadlocks Then ...


2

Single trigger function for multiple triggers: possible - that's what you do. Single trigger for multiple tables: not possible. But you can shorten the code for creating lots of triggers: DO $$ BEGIN EXECUTE ( SELECT string_agg('CREATE TRIGGER touch_users BEFORE UPDATE ON ' || quote_ident(t) || ' FOR EACH ROW WHEN (OLD.modification_time IS NOT ...



Top 50 recent answers are included