Checking the value of a field and updating it

Question

I'm a little confused about checking values in a database. What I want to do is see if a tuple such as the following exists:

job_num   |item_code   |invoice_num
------------------------------------
94834     |EFC-ASSOC-01|

The invoice_num is not null, it's blank: " ".

What I want is to find if such an entry exists, where the invoice_num is " " and update it with a number entered into a textbox. I'm a little confused about if statements and cases in SQL, so if someone could point me in the right direction, that would be great!

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

If you are only having problems with the SQL and depending on the datatype of the column, this should help you. If you need the entire tuple in your where clause it would look like:

SELECT * FROM MyTableName Where job_num = 94834 AND item_code = 'EFC-ASSOC-01' AND datalength(invoice_num) = 0

edited May 23 '17 at 12:19

Community

1
1

answered Aug 18 '11 at 19:00

Ben.Vineyard

1,149
8
14

score 1 · Accepted Answer · answered Aug 18 '11 at 19:10

One way to find such rows (or tuples) would be a query like:

SELECT job_num, item_code, invoice_num
FROM tablename
WHERE job_num = 94834 AND item_code = "EFC-ASSOC-01" AND invoice_num = ""

or follow @Ben's advice if the empty string is a problem. Then you can do an update:

UPDATE tablename SET invoice_num = ? WHERE job_num = .........

However, the problem with this approach is that if you're not using the primary key to choose a row in the update statement, multiple rows could get updated (similarly, the select statement could return multiple rows). So, you'll have to look at the database schema and determine the primary key column(s) of the table, and make sure that all of the primary key columns are used in the WHERE clause of the update. If you just do

UPDATE tablename SET invoice_num = value WHERE invoice_num = ""

all rows with that value of invoice_num will be updated, which may not be what you want.

Well, primary keys arent an issue as the job number and item code are the keys. I think this one will do it, I'll give it a shot. — MGZero, Aug 18 '11 at 19:18

score 0 · Answer 3 · answered Aug 18 '11 at 19:12

0

If you can pass the value you want to update on a variable (let's call it @RealInvoiceNum), then the UPDATE statement should be this:

UPDATE YourTable
SET invoice_num = @RealInvoiceNum
WHERE job_num = @JobNum AND invoice_num = ''

answered Aug 18 '11 at 19:12

Lamak

69,480
12
108
116

Checking the value of a field and updating it

3 Answers3