Question about SQL UPDATE query.

[meteos77]

I'm trying to update the callnumber field with 2 pieces of information found in location and authors.

the SELECT command gives the right results
SELECT SUBSTR(location, 0, INSTR(location, '-')) || ' ' || SUBSTR(author,0,4) FROM book

But when I want to go to the UPDATE step, it copies me the same value for all the books

UPDATE book SET callnumber = (SELECT SUBSTR(location, 0, INSTR(location, '-')) || ' ' || SUBSTR(author,0,4) FROM book)

Can you please give me some hints?
Thank you in advance.

[textbrowser]

Missing a WHERE clause. Somehow you'll need to identify which books to apply the update to.

[meteos77]

I was in the same situation as this example?

[meteos77]

I was looking for too complicated ...

UPDATE book SET callnumber = (SUBSTR(location,0, INSTR(location, '-')) || ' ' || SUBSTR(author,0,4))

[textbrowser]

Update queries can be applied to supersets or subsets.

[meteos77]

I didn't succeed with the WHERE clause
clause, I don't understand how to specify that all records must be processed line by line.

my functional request is a supersets ?

[textbrowser]

Huh? Someone is confused.

UPDATE table SET field_a = field_b || field_c will set field_a to CONCAT(field_b, field_c) (assuming text) for row 0, row 1, etc.

[textbrowser]

UPDATE table SET field_a = (SELECT ...) is not the solution you're looking for. Since the UPDATE of field_a can only be applied to a specific row, the UPDATE must select the first value from the inner SELECT. That is, row 0's field_a can only have one value; not all of the values in the table.

[meteos77]

Thanks for the explanations, I'll take a closer look as SELECT in UPDATE is still a bit obscure for me.

[textbrowser]

Don't perform an inner select. UPDATE field = another_field with an optional WHERE. You're updating a single table. The inner select doesn't have a WHERE clause. It's like updating all books to be like one book. Which book? Well, the first one the SELECT discovers. You want to set a book's title to the concatenation of its title with its author. The UPDATE will do that without a WHERE clause because it's smart. UPDATE book SET title = title || author.

[textbrowser]

sqlite> update book set title = title || author;
sqlite> select title from book;
An introduction to mathematical cryptographyHoffstein, Jeffrey. Pipher, Jill Catherine Silverman, Joseph H.
Handbook of applied cryptographyMenezes, A. J. Van Oorschot, Paul C. Vanstone, Scott A.
Introduction to modern cryptographyKatz, Jonathan Lindell, Yehuda.
Understanding cryptography : a textbook for students and practitionersPaar, Christof Pelzl, Jan.
A Sample Book (2)Author
A Sample Book (1)Author
Stories of Indian life.Anil Chandra Biswas
Fantastic Mr. FoxRoald Dahl
Quentin Blake
Ballpark eats :recipes inspired by America's baseball stadiumsJorgensen, Katrina
After Man (Expanded 40th Anniversary Edition)Dougal Dixon
Carbon :one atom's odysseyBarnett, John
Open circuits :the inner beauty of electronic componentsOskay, Windell
Schlaepfer, Eric
A Sample Book (3)N/A

[textbrowser]

SQL is very intuitive. Even the exceptions are self-explanatory when you think about them. SQL is Smart | Simple | Structured | Sophisticated Query Language.

[meteos77]

I built my UPDATE request using my first approach, which was to check whether the new value would be the right one.

It's probably the wrong way to go about it, because to make an UPDATE you have to start from scratch.