June 15, 2007 at 2:25 pm
I have a table with the following columns: Rtype, Amount, Refnumber, Color:
Here is what the table looks like:
RD 25 1blue
AB 45 2green
AC 25 3purple
AD 34 4blue
AE 25 5pink
Now lets say I would like to create new transactions for all 'RD' Rtypes, but I would like to change the Amount to lets say, 99 for all of 'RD' types, but keep the same refNumber. For example, I want to create and insert a new transaction for RD, but with the amount 99, so that the row looks like this:
RD 99 1 blue
Where do I put in the query to change the Amount value? The query I am using, but not working is:
INSERT INTO EMPLOYEES(Rtype, Amount, Refnumber, Color)
SELECT Rtype, Amount, Refnumber, Color
FROM Employees
WHERE Rtype = 'RD';
June 15, 2007 at 3:19 pm
Reading your post, I don't think you want to use INSERT. You don't want a new row:
RD 25 1 blue
AB 45 2 green
AC 25 3 purple
AD 34 4 blue
AE 25 5 pink
RD 99 1 blue
You just want to change the row that already exists:
RD 99 1 blue
AB 45 2 green
AC 25 3 purple
AD 34 4 blue
AE 25 5 pink
That is an UPDATE statement:
UPDATE EMPLOYEES
SET Amount = 99
WHERE Rtype = 'RD'
If you really want to create a new row, you are going to have to ensure you don't have a primary key on Rtype.
-SQLBill
June 15, 2007 at 3:30 pm
What I showed you was sample table of what I am trying to do. On the real table I am working on, I acutally want to create a new row for a new transaction, because for accounting situations, or atleast in this one you cannot delete tranasctions, but you can ammend them. Is there a way I can create a new row, as I described above? Thank you for the help, I appreciate it.
June 15, 2007 at 3:31 pm
I am not using Rtype as a primary key, so what type of SQL query should I use to create a new row?
June 15, 2007 at 7:34 pm
INSERT INTO EMPLOYEES(Rtype, Amount, Refnumber, Color)
SELECT Rtype, 99, Refnumber, Color
FROM Employees
WHERE Rtype = 'RD'
June 15, 2007 at 9:00 pm
Koji,
Awesome, let me try it out.
June 15, 2007 at 9:06 pm
Koji,
Worked perfectly thank you very much!
June 16, 2007 at 7:03 am
Hey All,
In my table I have a Amount column with values of -25.
I can select the -25 from the SELECT statement, however, I would like to do this
in the WHERE statement. The purpose is to return all amounts that have -25. Here is the script I am trying to run:
SELECT rowID_PK, Category, Type, Amount
FROM Transactions
WHERE Amount = '-25' AND Type LIKE 'DEP_%';
Thanks...
June 16, 2007 at 1:44 pm
Hopefully, your Amount column is one of the numeric datatypes and not character based... your query should work fine as is but the WHERE clause does not have the same criteria as the problem you described... you describe the problem as "return all amounts that have -25", but you query has the addititonal criteria for TYPE... did you mean to do that?
Also, like I said, hopefully your Amount column is numeric and not character based. If that's true, the following would work better because SQL won't have to make the implicit conversion from the character based '-25' that you have, to numeric...
SELECT rowID_PK, Category, Type, Amount
FROM Transactions
WHERE Amount = -25
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply