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
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'
SET No_Of_Kids = No_Of_Kids + 3
To increment number of kids:
UPDATE ParentKidsTable
SET No_Of_Kids = No_Of_Kids + 3
WHERE ParentName = 'some_value'
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'