sql-update
MySQL: update field only if condition is met
Yes! Here you have another example: UPDATE prices SET final_price= CASE WHEN currency=1 THEN 0.81*final_price ELSE final_price END This works because MySQL doesn’t update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it.
UPDATE with CASE and IN – Oracle
You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression). UPDATE tab1 SET budgpost_gr1= CASE WHEN (budgpost in (‘1001′,’1012′,’50055’)) THEN ‘BP_GR_A’ WHEN (budgpost in (‘5′,’10’,’98’,’0′)) THEN ‘BP_GR_B’ WHEN (budgpost in … Read more
Postgres update from left join
Here’s a generic way to transform this update query from SQL-server form to PostgreSQL: UPDATE Users SET bUsrActive = false WHERE ctid IN ( SELECT u.ctid FROM Users u LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1 WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr … Read more
Using cross apply in update statement
You where right, Albert. I made some tests and found that it’s possible, indeed. The use is the same as in a SELECT statement. For example: UPDATE st SET some_row = A.another_row, some_row2 = A.another_row/2 FROM some_table st CROSS APPLY (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id) AS A WHERE …
Does MySQL overwrite a column of same value on update?
As the MySQL manual for the UPDATE statement implies, If you set a column to the value it currently has, MySQL notices this and does not update it. So, if you run this query, MySQL will understand that the value you’re trying to apply is the same as the current one for the specified column, … Read more
SQL Inner join 2 tables with multiple column conditions and update
UPDATE T1 SET T1.Inci = T2.Inci FROM T1 INNER JOIN T2 ON T1.Brands = T2.Brands AND T1.Category= T2.Category AND T1.Date = T2.Date
MySql update two tables at once
It should be possible with a multi-table update, as described in the documentation. http://dev.mysql.com/doc/refman/5.5/en/update.html UPDATE Table_One a INNER JOIN Table_Two b ON (a.userid = b.userid) SET a.win = a.win+1, a.streak = a.streak+1, a.score = a.score+200, b.win = b.win+1, b.streak = b.streak+1, b.score = b.score+200 WHERE a.userid = 1 AND a.lid = 1 AND b.userid = … Read more
Table is ‘read only’
In my case, mysql config file had innodb_force_recovery = 1. Commenting that out solved the issue.