CODEWITHSUNDEEP
mysqlsqlcase
Parapluie
Parapluie

Reputation: 736

directory paths in mySQL

I am trying to pass a series of "correct" paths as strings to a mySQL database column.

Since multiple instances may be UPDATEd at the same time, I have chosen to use MySQL's CASE syntax, though I am not overly familiar with it.

The result passes "0" to the database, instead of the string.

I have seen numerous examples using integers as the cases, but I need to use strings. I think this is giving me my grief.

Can the SO community have a look and see where the issue may be?

Here is the echoed SQL from the debug line above:

UPDATE linksUpdates

 SET newTarget = CASE 

  WHEN hitMissed = '/dir/dir/misLink1'         THEN newTarget = 'dir/home' 
  WHEN hitMissed = '/dir/dir/misLink2'         THEN newTarget = 'photos' 
  WHEN hitMissed = '/dir/dir/anotherBadLink'   THEN newTarget = 'dir/home' 
  END 
 WHERE misdirect IN (
   '/dir/dir/misLink1',
   '/dir/dir/misLink2',
   '/dir/dir/anotherBadLink'
       )

Upvotes: 0

Views: 74

Answers (1)

ydaetskcoR
ydaetskcoR

Reputation: 56997

Your THEN in your CASE statement doesn't need (or want) the column name. You're already specifying this before you open the CASE.

UPDATE linksUpdates

 SET newTarget = (CASE 

  WHEN hitMissed = '/dir/dir/misLink1'         THEN 'dir/home' 
  WHEN hitMissed = '/dir/dir/misLink2'         THEN 'photos' 
  WHEN hitMissed = '/dir/dir/anotherBadLink'   THEN 'dir/home' 
  END)
 WHERE misdirect IN (
   '/dir/dir/misLink1',
   '/dir/dir/misLink2',
   '/dir/dir/anotherBadLink'
       )

Upvotes: 1

Related Questions