Update a cell that contains a specific string where the ROW is SPECIFIED and COLUMN is NOT SPECIFIED-CodePudding

I have a table with the following columns: name - course1 - course2 - course3. Two rows look like this:

John - physics - math - art
Sara - math - chemistry - psychology

Now John has been expelled from the math class and I want to replace "math" with "none" on his row.

When I look for a solution I find things like this:

UPDATE tableName SET `course1` = 'none' WHERE `name`='John' AND `course1`='math';

That could be useful if I knew the column where 'math' was recorded for John. But that word can be under any column. What I need is something like this:

sql_query="find the row where name='John' and then find the column where we have the word 'math' and only there replace 'math' with 'none'.

Can you kindly help me with this?

CodePudding user response：

In this case, I think there is no other way besides evaluating each column, like this:

update
   my_table
set 
   course1 = if(course1 = 'math', 'none', course1),
   course2 = if(course2 = 'math', 'none', course2),
   course3 = if(course3 = 'math', 'none', course3)
where
   name = 'John';

CodePudding user response：

First of all, if your table i.e tbl_Student don't have an ID which is a Primary Key you are in big trouble, I am recommending you to have something like this, again I don't know what you are storing so keep in mind change it to what you need, but with a Primary Key: please also note I have some changes for your table structure as well, I am starting with the simplest one

tbl_Student

--------------------
sid   sName   cName
--------------------
1     Shaho   Math
2     Awat    Physics

now, I want to change Shaho's cName to anything, in your case 'none' use this.

update tbl_Student set cName = 'none' where sid = '1'

because sid is a Primary Key you don't have to worry about duplicate here, since the Primary Key has two main characteristics Not Null and No Duplicate:

let me change some structure for you if you see in your provided example, the courses starts from One to unknown, might be two, or three or 10 you can do c1,c2,c3...c10, but it is not a good practice, since you are using a relational database, we can use that, let me show you an example:

tbl_Student

--------------------
sid   sName   
--------------------
1     Shaho   
2     Awat

tbl_Course

--------------------
cid   cName   
--------------------
1     Math   
2     Physics

here is the inner table

tbl_StudentCourse

--------------------
scid   sid    cid   
--------------------
1     1       1   
2     1       1
3     2       1

As you see you can have almost any course assigned to a specific student, but How I can get information for a student since they don't have a direct connection between tables?

well, we have one, the inner table tbl_StudentCourse which connects both of them, so, here we can use the join techniques to get whatever information we want, I want to select sid, sName, came for student one

SELECT tbl_student.sid, tbl_student.sName, tbl_course.cName from tbl_student left join tbl_studentcourse on (tbl_student.sid = tbl_studentcourse.sid) left join tbl_course on (tbl_course.cid = tbl_studentcourse.cid) where tbl_student.sid = 1

if you want all students:

SELECT tbl_student.sid, tbl_student.sName, tbl_course.cName from tbl_student left join tbl_studentcourse on (tbl_student.sid = tbl_studentcourse.sid) left join tbl_course on (tbl_course.cid = tbl_studentcourse.cid);

So, we are using this, why we don't use names or courses?

well, the answer for this is the process on string is much slower rather than the number, and you still might get duplicate values.

On the other hand, using a Key that is not null and not duplicated will change the entire game for us. the other of using relational one is each student is now unique, each Course is now unique, you just assign which student has which course, also if you see just from looking, all other operation now is easier, do you want to update student name? then update it.

My advice is to use Relational, if you don't then go back to Excel just kidding.