sql - update table question

Question

Hello everyone, Merry Christmas!

Someone still visiting the forum?

Could you pls take a look at the following language? Where is the problem?

ALTER TABLE cities ADD AttractivePlace VARCHAR;
UPDATE cities
SET AttractivePlace ='Belem Tower'
WHERE name ='Lisbon' ;
UPDATE cities
SET AttractivePlace ='Plaza Mayor '
WHERE name ='Madrid' ;
UPDATE cities
SET AttractivePlace ='Eiffel Tower'
WHERE name ='Paris';
SELECT * FROM cities;

Answer 1

I think it is a simple typing error in the query. The table name is "leaderboard", but your query was trying to select from "learderboard". There is an extra 'r' in the table name used in the query.

Answer 2

Your Output
name,country,population,attractiveplace
Lisbon,Portugalia,504718,Belem Tower
Madrid,Spain,3223000,Plaza Mayor
Paris,France,2161000,Eiffel Tower
Expected Output
name,country,population,attractiveplace

Lisbon,Portugalia,504718,Belem Tower

Madrid,Spain,3223000,Plaza Mayor

Paris,France,2161000,Eiffel Tower

Answer 3

Brian you save life!! I check several times and didn’t find this error😂😂

Answer 4

Glad I could help! :D

Answer 5

YANG Fei since you replaced the question with a new one, now the old answers do not match. It will confuse future readers. You should have started a new question.

Answering your new question:

The output data look correct. I think you are asking how to format the output to have double-spaced lines instead of single-spaced. Normally that is a feature of your IDE or report software.

If you need it done as part of the query, then I can give you a hack that might work. Since the last field is a string type, then possibly you could concatenate to it (+) the literal constant ASCII characters 13 and 10, which are carriage return (CR) and line feed (LF) respectively. Use the CHR function in the SELECT statement to convert from their numeric value into the characters.

Answer 6

@Brian
I updated my question, can you help?

Answer 7

I still don't get it!!

Answer 8

ALTER TABLE cities
ADD AttractivePlace VARCHAR(255);

UPDATE cities
SET AttractivePlace =
CASE
WHEN name = 'Lisbon' THEN 'Belem Tower'
WHEN name = 'Madrid' THEN 'Plaza Mayor'
WHEN name = 'Paris' THEN 'Eiffel Tower'
END;

SELECT * FROM cities;

check out this Query
Its working