sql statement

mysql 5.0


correct sql statemnt to change a column from yes to no
LVL 8
thenoneAsked:
Who is Participating?
 
lojkConnect With a Mentor .Net and Infrastructure ConsultantCommented:
you would generally never do such a thing... It causes all sorts of record locking issues for starters not to mention the fact that it is *slow*

you would already have dimmed a connection to connect your rs anyway, so why not just do..

dim MyConn as new adodb.connection
myconn=whatever
myconn.execute ("UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `keyfield` = <keyvalue> AND `thecolumn` = 'yes' ")

as angel had already posted?


also, if your DB is access and this is a boolean field rather than text (as it should be) you may actually need to do...

rs.fields("column").value = true


0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>correct sql statemnt to change a column from yes to no

put in here the correct name of the table and the columns:

UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `keyfield` = <keyvalue> AND `thecolumn` = 'yes'

0
 
thenoneAuthor Commented:
thanks angel
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
thenoneAuthor Commented:
and if I do not have a primary key?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and if I do not have a primary key?
how do you identify the row to update then?
0
 
Raynard7Commented:
UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `thecolumn` = 'yes'

This will update everything - regardless of the id
0
 
thenoneAuthor Commented:
and if I was doing it with a recordset?
0
 
thenoneAuthor Commented:
ok so if I have a recordset where it is rs.open"select * from table1 where column=no"

then when I am looping through my records how can I have the recordset be able to change that column one at a time from no to yes.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
rs.open "select * from table1 where column=no"
while not rs.eof
   rs.fields("column").value = "yes"
   rs.update
   rs.movenext
wend
rs.close
0
 
thenoneAuthor Commented:
so just this?

rs.fields("column").value = "yes"

ok thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.