yaylitzis yaylitzis - 5 days ago 5
SQL Question

Update or not a column, if select returns a record

I have two tables:

LABELS
table has two columns
oldLabel
and
label
and the other table
INVENTORY
has a column
label
which contains the values of the column
LABELS.oldLabel
.

I want to update the values of the column
INVENTORY.label
with the values of
LABELS.label
. I tried:

UPDATE INVENTORY
SET label = (SELECT label FROM LABELS AS T2 WHERE T2.oldLabel = INVENTORY.label);


which works but if the
SELECT
doesn't return a value, then the
INVENTORY.label
gets a null value. How can I alter the above query, so when the
SELECT
doesn't return a record, don't update that record!

I found a way which I use some
JAVA
implementation..

sq = "UPDATE INVENTORY "
+ "SET label = ? WHERE label = ?";
stm = c.prepareStatement(sq);

for (Label label : labels) { //labels is an arrayList which contains both values
stm.setString(1, label.getLabel());
stm.setString(2, label.getOldLabel());

result = stm.executeUpdate();
}


However, can I do this with SQL code only?

CL. CL.
Answer

Just add a WHERE clause to the UPDATE:

UPDATE INVENTORY
SET label = (SELECT ...)
WHERE EXISTS (SELECT ...);
Comments