CODEWITHSUNDEEP
sqloracle-database
Supra
Supra

Reputation: 3

SQL Query for an update of a column based on other column's data in a Table

I need to write an SQL query to take either from one of two column's data(based on whichever is available and not null) and append it to a static text into another column within the same table.

Can anybody tell me how to write this?

Example Data :

ID  Type   Barcode  Serial No  Location

1   Test    ABCD     1234       LOC1  
2   Test    EFGH     NULL       LOC2  
3   Test    NULL     5678       LOC3  
4   Test    NULL     NULL       LOC1

Final Data Reqd in Format

ID  Type  Barcode  Serial No  Location

1   Test  ABCD     1234       LOC1-ABCD   (Append barcode if its not null)  
2   Test  EFGH     NULL       LOC2-EFGH   (Append barcode if its not null)  
3   Test  NULL     5678       LOC3-5678   (Append serial no since barcode is null)  
4   Test  NULL     NULL       LOC1        (Both r Null keep loc as it is)

Please help me on it....really stuck on this :(

Database is Oracle 10.

Supra

Update 1 :

Thanks a lot Marco for your help....the location field only needs to updated in the table after appending the barcode/serial no from the same table into location field. Your edited query is not working :(...please let me know if I need to give some more info/data.

Final Update :

Shesek's Answer worked perfect :D...you are the man :)...Thanks a ton :D

Upvotes: 0

Views: 3686

Answers (3)

shesek
shesek

Reputation: 4682

According to your comment on the other answer,

UPDATE Network_Plant_Items
    SET FULL_ADDRESS = 'foobar' || COALESCE(BARCODE, MANUF_SERIAL_NUMBER)
    WHERE BARCODE IS NOT NULL OR MANUF_SERIAL_NUMBER IS NOT NULL

If you want to append this to the current value of FULL_ADDRESS, as I understand from the original question,

UPDATE Network_Plant_Items
    SET FULL_ADDRESS = FULL_ADDRESS || COALESCE(BARCODE, MANUF_SERIAL_NUMBER)
    WHERE BARCODE IS NOT NULL OR MANUF_SERIAL_NUMBER IS NOT NULL

COALESCE() returns the first non-NULL argument you pass to it. See Oracle's manual page on it.

Just as a general FIY, NVM() that was suggested by another answers is the old Oracle-specific version of COALESCE(), which works kinda the same - but it only supports two arguments and evaluates the second argument even if the first one is non-null (or in other words, its not short-circuit evaluated). Generally, it should be avoided and the standard COALESCE should be used instead, unless you explicitly need to evaluate all the arguments even when there's no need for it.

Upvotes: 0

Billy Cravens
Billy Cravens

Reputation: 1663

Don't have Oracle installed, so can't test query, but this should work....

You could use NVL function, but it's good for 2 options (use value a if it's not null, and if it is, use value b)

update myTable
set myCol = NVL(colA,colB)

Since you however want to concatenate if and only if you get a non-null value from either column, you need to get a little trickier and combine using nested NVL()

update myTable
set myCol = myCol || CASE NVL(NVL(colA,colB),0) WHEN 0 THEN '' ELSE '-' || NVL(colA,colB)

Explanation:

  • NVL(colA,colB) will evaluate to colA first, colB if colA is null, and null if both are null
  • In all situations I return the column's value
  • I then append - if both are null, I essentially am appending 2 empty strings ('' and NULL); otherwise, I'm appending '-' and the value I evaluate to
  • I use a CASE statement to do an IF .. ELSE to figure if I need '-'
  • I use nested NVL functions to assign 0 if both are null to make the CASE logic easy
  • I then append my evaluated value

As I said before, this is untested, so there could be some syntax issues, but the logic is correct :-)

Upvotes: 0

Marco
Marco

Reputation: 57573

Try this:

SELECT ID, Type, Barcode, "Serial No",
CASE
  WHEN Barcode IS NOT NULL THEN Location || '-' || Barcode
  WHEN "Serial No" IS NOT NULL THEN Location || '-' || "Serial No"
  ELSE Location
FROM your_table

Take a look at CASE function and String Concat.
I'm not sure the way I escaped Serial No field, anyway look here

EDITED:
Try this:

UPDATE your_table SET Location =
CASE
  WHEN Barcode IS NOT NULL THEN Location || '-' || Barcode
  WHEN "Serial No" IS NOT NULL THEN Location || '-' || "Serial No"
  ELSE Location

Upvotes: 1

Related Questions