EQEmulator Forums - SQL code help

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Development::Database/World Building (https://www.eqemulator.org/forums/forumdisplay.php?f=596)

- - SQL code help (https://www.eqemulator.org/forums/showthread.php?t=39227)

Hi
I am playing around with a solo server for now, and wish to attemp a global DB change (to see if I can).
I want to update an entry in table1 based on an entry in table2

Code:

UPDATE lootdrop_entries SET chance = 50 IF items COLUMN name = *defiant*;

- I know its very wrong

ie if in the items table column name it says defiant then change the lootdrop_entries table column chance to be 50%

I know the tables are linked by item_id so they should connect

Also would this back up a single table prior to me messing with it?

Code:

SELECT * INTO backuplootdrop_entries FROM lootdrop_entries;

Again any help gratefully received

For backup you could say:
create table backuplootdrop_entries as select * from lootdrop_entries;

This will make a copy of the lootdrop_entries table.

I am not in front of my eqemu computer but i beleive this will work for your chance adjustment

Code:

update lootdrop_entries set chance = 50 where item_id in (select id from items where name regexp '(Crude|Simple|Rough|Ornate|Flawed|Intricate|Elaborate|Elegant)( Defiant)');

Superb - thank you makes sense when I read it but never would have got it, and so over to the dark side of sql