UPDATE multicolumns in TABLE

[PriteshP23]

Update is not working.. 

UPDATE Customers

SET ContactName='Alfred Schmidt', City='Hamburg'

WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

Thanks in advanced.

[Muddy_Funster]

what happens when you run the query? do you get any errors?

[PriteshP23]

Not errors. But it is not working.

[PriteshP23]

After running above query:

 

" Number of records affected: 0 (Processing 1.5935 sec.) "

[mac_gyver]

that would mean that your WHERE clause if false. have you looked in your database table to see if there's a row with those values in it?

[PriteshP23]

YES, i did. Thats why i want to update the table.

[Psycho]

What does this query return?

 

SELECT COUNT(*)
FROM Customers
WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

[PriteshP23]

COUNT(*) 4

[PriteshP23]

There are multi-rows i need to update for sure. I have already cross check all values.

[mac_gyver]

i'll add an edit to my reply #5 - ... OR it means that the columns already have the values you are trying to update them to.

[PriteshP23]

Based on CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste' VALUES only i have to update other columns like City. But by default city is NULL.

 

I hope i am clear.

[mikosiko]

post your full table definition and the complete results of this query:

SELECT *        (or at least "CustomerFirstName, CustomerLastName, ContactName and City")
FROM Customers
WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

[jazzman1]

COUNT(*) 4

 

Hm.....it's very weird! What SQL editor are you using to achive that?

Anyways, it sounds like ( not sure) the SQL_SAFE_UPDATES is set up to ON.

So, before to send an update command to the sql server, run that:

SET SQL_SAFE_UPDATES=0;

[PriteshP23]

post your full table definition and the complete results of this query:

SELECT * (or at least "CustomerFirstName, CustomerLastName, ContactName and City")

FROM Customers

WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

 

 

ID     CustomerFirstName     CustomerLastName     ContactName     City     Company

 1                Alfreds                      Futterkiste                  NULL                    NULL        Dell

 3               Alfreds                       Futterkiste                  NULL                    NULL        Dell

 4               Alfreds                       Futterkiste                  NULL                    NULL        Dell

 5               Alfreds                       Futterkiste                  NULL                    NULL        Dell

 

Hm.....it's very weird! What SQL editor are you using to achive that?

 

I'm using database: MySQL client version: 5.0.45

 

I tried SET SQL_SAFE_UPDATES=0;   Can you tell me more about SAFE UPDATES ??

 

Still it's not working..

[mikosiko]

You didn't post the full definition of your table...the one that start with CREATE TABLE.... etc..etc

[PriteshP23]

You didn't post the full definition of your table...the one that start with CREATE TABLE.... etc..etc

 

 

Here it is:

CREATE TABLE IF NOT EXISTS `Customers` (
  `ID` int(10) NOT NULL auto_increment,
  `CustomerFirstName` varchar(30) default NULL,
  `CustomerLastName` varchar(30) default NULL,
  `ContactName` varchar(30) default NULL,
  `City` varchar(30) default NULL,
  `Company` varchar(20) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Cutomers Data ' AUTO_INCREMENT=7 ;

[jazzman1]

 

 

I tried SET SQL_SAFE_UPDATES=0;   Can you tell me more about SAFE UPDATES ??

 

It's a useful startup option for beginners in sql, which allows you to update(delete) rows only by specifying the key values that identify them, like in your example!

 

So, I don't see nothing wrong in your database structure. I am out.

[PriteshP23]

In database, I wrote:

UPDATE Customers

SET ContactName='Alfred Schmidt'  AND City='Hamburg'

WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

It must be:

UPDATE Customers

SET ContactName='Alfred Schmidt'  , City='Hamburg'

WHERE CustomerFirstName='Alfreds' AND CustomerLastName='Futterkiste'

[mac_gyver]

LOL, the query you posted at the start of this thread is the correct syntax and we only see and can only help you based on the information you provided in your posts.

 

in the future, copy paste the actual query/code you need help with to avoid wasting everyone's time.

[mikosiko]

how that old say goes?..... "if everything looks right, then for sure you will find the issue between the keyboard and the chair"