I have the following MySQL code, which should (for each row) take the value of the column Cislo and then update the current row, setting the column Cislo and Nazov to SUBSTRINGs of the current value.
So let’s say currently
Cislo = 12345LoremIpsum and Nazov='', after the update it should be Cislo=12345 and Nazov=LoremIpsum.
I have tried many different approaches, however the results are always empty cells.
UPDATE `skladovekartyvyrobok00001` SET
`Cislo`= SUBSTRING(`Cislo`,0,5),
`Nazov`= SUBSTRING(`Cislo`,5)
WHERE `ID`=31;
>Solution :
The second parameter of SUBSTRING might start as 1 instead of 0
A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.
Then you can use a little trick to update Nazov column first and then update Cislo
I think you might try this.
UPDATE `skladovekartyvyrobok00001` SET
`Nazov`= SUBSTRING(`Cislo`,6),
`Cislo`= SUBSTRING(`Cislo`,1,5)
WHERE `ID`=31;