CODEWITHSUNDEEP
mysqlsql
king theking
king theking

Reputation: 53

What is the meaning of "UPDATE table SET column1 = value AND column2 value WHERE condition" in SQL?

If the "," in an UPDATE statement is replaced with AND, what the meaning in SQL?

Normal Update Statement : 

UPDATE table 
SET column1 = value , 
    column2 = value 
WHERE condition

My question statement : 

UPDATE table 
SET column1 = value AND column2 = value 
WHERE condition

The affected rows seem to be different, so I want to know what my question statement mean in SQL?

Original test Data

After inserting query(from original) : Update testing set column1 = 2 , column2 =5

Normal Update

After inserting query(from original) : Update testing set column1 = 2 AND column2 =5

My Question Statement

if I insert the query(from original) :Update testing set column1 = 2 AND =4

the output

Upvotes: 1

Views: 861

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

It's probably wrong to write 2 AND b = 1 though some databases evaluate it and others don't

If your intention is to set multiple columns in one go, you MUST use a comma

If you use this construct, perhaps some databases will process it according to bitwise-logical operation or they will treat any non zero value as true/false and any zero as false/true, and others will process it according to Boolean-logical operation

In the example strawberry kindly posted: https://www.db-fiddle.com/f/3KawkrD8QfjJu6YyfuzB7U/0

Set the db to MySQL and run it; it works out - MySQL is probably treating 2 as true, so the operation becomes: 

SET c = true and true --when b = 1
SET c = true and false --when b=0

You can see your c column is set to 1 or 0 depending on the truth

SET c = (2 AND (b = 1)) 
        ^^^^^^^^^^^^^^^
        This whole thing is turned into a value. 
        for C, it does NOT set column B at all

Now change the DB to Postgres and run it again

This time you get an error that AND expects Boolean operands, and the integer 2 is not acceptable

Hence in Postgres while this might be acceptable:

SET d = (c=1 AND b=2) --sets a true or false value for d

Your other form is not acceptable

--

Long story short, you probably intended to set multiple columns: use a comma.

Upvotes: 3

Strawberry
Strawberry

Reputation: 33945

We can see from this simple experiment...

https://www.db-fiddle.com/f/3KawkrD8QfjJu6YyfuzB7U/0

...that ...

UPDATE my_table SET c = 2 AND b = 1;

... is interpreted as...

UPDATE my_table SET c ........ = 1;

Upvotes: 1

Related Questions