-1

I have a license table that contains the type of license and the license number. The user was supposed to type in the entire license number (including type and number) as shown on their license. I inherited a database with some who followed the rules and some who did not. See the table below for samples.

type number
RN 2676612
PN 1234567
RN RN2676612
PN PN1234567

Only the third and fourth entries are correct. I am trying to find entries that are like the first two entries and update the number field to contain the type. So, find the first and second rows and update them to the third and fourth rows respectively.

I tried

Select * from licenses WHERE number NOT LIKE type+'%'

to try and select them before trying an update query, and I get error #1064. I have found several solutions that find if the value in one column is present anywhere in another column, but I need to know if the number field contains the type field from the SAME ROW.

Henry Geiter
  • 103
  • 1
  • 10

3 Answers3

1

CONCAT is how you add strings in MySQL. So:

SELECT * FROM licenses WHERE number NOT LIKE CONCAT(`type`, '%')
IT goldman
  • 14,885
  • 2
  • 14
  • 28
1

Try using POSITION(substr IN str).

 SELECT * FROM licenses WHERE POSITION(type IN number)>0
Filippo Favaro
  • 44
  • 5
1

I would just do an update

UPDATE licenses SET number=CONCAT(type, number) WHERE number NOT LIKE CONCAT(type, %)
Albert
  • 101
  • 5