Does replace into have a where clause?

by

I can see that you have solved your problem, but to answer your original question:

REPLACE INTO does not have a WHERE clause.

The REPLACE INTO syntax works exactly like INSERT INTO except that any old rows with the same primary or unique key is automaticly deleted before the new row is inserted.

This means that instead of a WHERE clause, you should add the primary key to the values beeing replaced to limit your update.

REPLACE INTO myTable (
  myPrimaryKey,
  myColumn1,
  myColumn2
) VALUES (
  100,
  'value1',
  'value2'
);

…will provide the same result as…

UPDATE myTable
SET myColumn1 = 'value1', myColumn2 = 'value2'
WHERE myPrimaryKey = 100;

…or more exactly:

DELETE FROM myTable WHERE myPrimaryKey = 100;
INSERT INTO myTable(
  myPrimaryKey,
  myColumn1,
  myColumn2
) VALUES (
  100,
  'value1',
  'value2'
);

More Related Contents:

  1. Out of memory Microsoft VBScript runtime error 800a0007
  2. Left Outer Join doesn’t return all rows from my left table?
  3. How can i optimize MySQL’s ORDER BY RAND() function?
  4. Are table names in MySQL case sensitive?
  5. 1114 (HY000): The table is full
  6. Get table names using SELECT statement in MySQL
  7. ERROR 1396 (HY000): Operation CREATE USER failed for ‘jack’@’localhost’
  8. UPDATE multiple tables in MySQL using LEFT JOIN
  9. Can a foreign key reference a non-unique index?
  10. How to raise an error within a MySQL function
  11. What is maximum query size for mysql?
  12. Storing Data in MySQL as JSON
  13. Finding all parents in mysql table with single query (Recursive Query)
  14. SQL keys, MUL vs PRI vs UNI
  15. How do I retrieve my MySQL username and password?
  16. How does a MYSQL Self-Join Work?
  17. How to change the default port of mysql from 3306 to 3360
  18. How to move columns in a MySQL table?
  19. Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
  20. What is the best MySQL collation for German language
  21. quick selection of a random row from a large table in mysql
  22. MySQL error #2014 – Commands out of sync; you can’t run this command now
  23. How can we find gaps in sequential numbering in MySQL?
  24. Row to column transformation in MySQL
  25. Find date range overlaps within the same table, for specific user MySQL
  26. MySQL on delete cascade. Test Example
  27. mysql select id and name from other table and join query
  28. List of Constraints from MySQL Database
  29. MySQL Recursive get all child from parent
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment