Add a WHERE clause UPDATE dbo.TestStudents SET LASTNAME = CASE WHEN LASTNAME = ‘AAA’ THEN ‘BBB’ WHEN LASTNAME = ‘CCC’ THEN ‘DDD’ WHEN LASTNAME = ‘EEE’ THEN ‘FFF’ ELSE LASTNAME END WHERE LASTNAME IN (‘AAA’, ‘CCC’, ‘EEE’)
The syntax for using an alias in an update statement on SQL Server is as follows: UPDATE Q SET Q.TITLE = ‘TEST’ FROM HOLD_TABLE Q WHERE Q.ID = 101; The alias should not be necessary here though.
In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example: UPDATE tableB INNER JOIN tableA ON tableB.name = tableA.name SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value) WHERE tableA.name=”Joe”
This is called a correlated update UPDATE table1 t1 SET (name, desc) = (SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id) WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id ) Assuming the join results in a key-preserved view, you could also UPDATE (SELECT t1.id, t1.name name1, t1.desc desc1, t2.name … Read more
Escape the apostrophe (i.e. double-up the single quote character) in your SQL: INSERT INTO Person (First, Last) VALUES (‘Joe’, ‘O”Brien’) /\ right here The same applies to SELECT queries: SELECT First, Last FROM Person WHERE Last=”O”‘Brien’ The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string … Read more
Yes, that’s possible – you can use INSERT … ON DUPLICATE KEY UPDATE. Using your example: INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
Postgres allows: UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ …) AS subquery WHERE dummy.address_id=subquery.address_id; This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Note, the parentheses are required for UPDATE statements: update top (100) table1 set field1 = 1
SELECT DISTINCT a,b,c FROM t is roughly equivalent to: SELECT a,b,c FROM t GROUP BY a,b,c It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful. For your query, I’d do it like this: UPDATE sales SET status=”ACTIVE” WHERE id IN ( SELECT id FROM sales S INNER JOIN … Read more
You can actually do this one of two ways: MySQL update join syntax: UPDATE tableA a INNER JOIN tableB b ON a.name_a = b.name_b SET validation_check = if(start_dts > end_dts, ‘VALID’, ”) — where clause can go here ANSI SQL syntax: UPDATE tableA SET validation_check = (SELECT if(start_DTS > end_DTS, ‘VALID’, ”) AS validation_check FROM … Read more