CODEWITHSUNDEEP
sql
Xavi Lopez Ponte
Xavi Lopez Ponte

Reputation: 69

SQL statement to update and set

I would like to update a register in a database using a SQL Query

UPDATE bin SET binname = 'jejej' AND capacity = '8' WHERE binname = 'test' AND binid = '1'

I would like to update field binname and capacity to those values in case there is a binname = test and binid = 1

However, the register which exists in the base changes to binname = 0 and capacity do not change.

How can I create a good SQL statement for that? BINID is a integer, binname = text, capacity = real.

Thanks and Kind regards

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

This code is syntactically incorrect in most databases:

UPDATE bin
    SET binname = 'jejej' AND capacity = '8'
    WHERE binname = 'test' AND binid = '1';

It would be interpreted as:

UPDATE bin
    SET binname = ('jejej' AND capacity = '8')
    WHERE binname = 'test' AND binid = '1';

That is binname would be set to the result of a boolean expression. If your database supports boolean expressions, then binname is set to true or false (which get converted to 1 and 0). However, it would more commonly be an error.

The correct syntax for updating multiple columns uses a comma:

UPDATE bin
    SET binname = 'jejej',
        capacity = '8'
    WHERE binname = 'test' AND binid = '1';

Note: If capacity and binid are numbers, then the associated literal values should be numbers and not strings:

UPDATE bin
    SET binname = 'jejej',
        capacity = 8
    WHERE binname = 'test' AND binid = 1;

Upvotes: 3

Sergey
Sergey

Reputation: 5225

 UPDATE bin SET binname = 'jejej',capacity = '8' WHERE binname = 'test' AND binid = '1'

Upvotes: 0

Related Questions