• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

sql statement

mysql 5.0


correct sql statemnt to change a column from yes to no
0
thenone
Asked:
thenone
2 Solutions
 
Guy Hengel [angelIII / a3]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
 
thenoneAuthor Commented:
and if I do not have a primary key?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
lojk.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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now