CODEWITHSUNDEEP
mysqlsql
Illep
Illep

Reputation: 16859

Writing a SQL Update statement

I need to know how to write a SQL statement for the following senario;

I have a table, which consist of 2 columns. ParentName and No_Of_Kids. so say we have a record called parent A and parent A had 2 kids.(in 2008)

Now in 2011 Parent A has got another 3 kids, so i need to update this table, to say Parent A has 5 kids altogether.

UPDATE ParentKidsTable
SET parentName=value, No_Of_kids=??? // (how to write the SQL no. of Kids in 2008 + no. of kids in 2011)
WHERE parentName=some_value

Upvotes: 1

Views: 593

Answers (3)

Chris Bailey
Chris Bailey

Reputation: 4136

I'm assuming that you're looking for the generic case of adding N kids, so you don't have to look up the value No_Of_Kids first in which case you can do:

UPDATE ParentKidsTable
SET No_Of_Kids = No_Of_Kids + 3
WHERE ParentName = 'some_value'

Upvotes: 1

Mariusz Sakowski
Mariusz Sakowski

Reputation: 3280

To increment number of kids:

UPDATE ParentKidsTable
SET No_Of_Kids = No_Of_Kids + 3
WHERE ParentName = 'some_value'

Upvotes: 2

Jules
Jules

Reputation: 7233

The general statement to update a table is:

UPDATE table
SET field = 'newValue'
WHERE otherfield = 'condition'

So in your case this makes:

UPDATE ParentKidsTable
SET No_Of_Kids = 5
WHERE ParentName = 'some_value'

This would update the No_Of_Kids to 5 where the ParentName equals some_value. Make sure you put the String literals between apostrophes.

If you want to add a certain number of kids to the yet existing number you can do:

UPDATE ParentKidsTable
SET No_Of_Kids = (No_Of_Kids + 3)
WHERE ParentName = 'some_value'

which is basically short (and better) for:

UPDATE ParentKidsTable
SET No_Of_Kids = ((SELECT No_Of_Kids 
                  FROM ParentKidsTable 
                  WHERE ParentName = 'some_value') + 3)
WHERE ParentName = 'some_value'

Upvotes: 2

Related Questions